Month Year Date Format

[Guest]

I am using Excel 2002. I have a document that is protected. Cell B2 is
unlocked so the user can input the month. What I would like to do is have
the date appear as: month year (e.g., February 2005). I need this to work
not only for the current month, but for the previous month, if the user needs
to input information for the previous month.

I've tried formatting the cell, using the Custom Category, mmmm yyyy.
However when I type February the year does not show. if I type just a 2 the
date shows up as January 1900. If possible I would like the month and year
to appear if the user types a number for the month (2 for February) or spells
out the month (February).

Thanks

 

[Bernie Deitrick]

Jamie,

You need to enter a valid date in that cell.

Simply enter 2/1 (or 2/1/5, or any value from 2/1 to 2/28) and it will show
up as February.

Enter 1/1 and it will show up as January.

HTH,
Bernie
MS Excel MVP

 

[Arvi Laanemets]

Hi

Add an additional sheet, p.e. SetUp (later you can hide the sheet, as the
user don't need the access to it). Into A1 enter the formula
=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)
Into A2 enter the formula
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

Format both cells in any valid date format displaying month (and year) - in
this format you see later selections in drop-down list.
Select both cells, and define the selected area as named range, p.e. Month

Select the cell B2 on your original sheet, and format it as data validation
list with source:
=Month
and format the cell in desired date format, p.e. "mmmm yyyy"

Now you can always select between current and previous month in cell B2, or
you can enter manually the 1st date of those months in any valid date
format - no other entries are allowed at all.


Arvi Laanemets