DATES: user enters "05/01", I want it displayed: May-2001

J

Jacob

is this possible?

If I choose the date format: "mmm-yy" and I type in "05/01", it returns
"May-04".

Thanks for your help on this simple matter,
Jacob
 
J

Jacob

Arvi:

It doesn't work. If I set the format to Custom, and then mmm-yyyy, and
I type "05/01" it returns May-2004. The year, unless AFTER 2004,
always returns a 2004 date.
Why is this???
 
M

Myrna Larson

How Excel interprets what you type is almost totally independent of the cell
format (but see below).

One of the built-in rules regarding date entries is is that if you type a
month, a date separator, then a number that is a legal day of the month,
that's how it is interpreted. Since you've omitted the year, Excel assumes you
mean the current year. Changing the cell format doesn't affect any of that.

OTOH, if you type 5/2004, 2004 can't be a day of the month, so it's
interpreted as the year, the day of the month is assumed to be 1, and the date
you see in the formula bar is 5/1/2004

If you want 5/01 to mean the month of May, year 2001, you must type it as
5/2001, in which case the date will be recorded as May 1, 2001. Note that a
date must include all 3 parts: year, month and day. Excel does use some
defaults, as I mentioned above: current year if that's missing, 1 as day of
month if that's missing.

*The exception to my first sentence is a cell that's formatted as Text.
 
Top