Date Format m/yy

[dread]

I can't seem to find the correct date format. I put in a custom format of
m/yy. I type in 10/04 (for October 2004) and the cell displays 10/07 and the
formula bar shows 10/4/2007. I want 10/04 to display. I'm using Excel 2002
SP3 in case that matters.

Thanks for the help.

 

[RichN]

type in 10/1/04. remember that excel considers date to consist of m-d-y.
you are creating a custom format - to show some of this info, not all (not
the "d").

when you type 10/4, excel thinks you mean 10/4/07. it allows you to only
type in m-d if you want the current year.

 

[Chip Pearson]

You can't have an incomplete date in Excel. That is, there no way to store
the value "October 2004" without specifying a day of month. You need to
include the day of month when you enter the value. E.g,. enter 10/1/04 and
format with m/yy


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[JE McGimpsey]

First, display format has *nothing* to do with how the parser interprets
your input (except that setting it to Text bypasses the parser
altogether). So your display date format doesn't matter.

XL will try to make partial dates fit the current year if possible
(which is why 10/04 ==> 10/04/07 but 4/40 ==> 4/1/1940

See XL Help's "About dates and date systems"/"How Excel interprets
incomplete or ambiguous date entries" for more.

 

[Trevor Shuttleworth]

Excel is just trying to be helpful.

When you put in something that looks like it is a date or part of a date,
Excel will format it as a date and try to fill in the gap. The "/" is the
clue that Excel is looking for to indicate a date.

I'm assuming that you're entering your date in Month, Day, Year format ...
hence month 10, day 4 in your example. Excel perceives the gap to be the
year and kindly adds 2007. Thus your display is month 10, year 07 ... and
throws away the day 04.

I'm not sure there is a way round this. Simplest way would be to type
10/1/4 to get 10/04. Probably not what you want to hear.

Regards

Trevor

 

[dread]

Thank you. That worked great!

type in 10/1/04. remember that excel considers date to consist of m-d-y.
you are creating a custom format - to show some of this info, not all (not
the "d").

when you type 10/4, excel thinks you mean 10/4/07. it allows you to only
type in m-d if you want the current year.