Date defaults to current year

[Guest]

The date format is set to "Mar-01", and when the date is entered in any of
these formats: "Mar-08", "Mar 08", "3/08", "3-08" it defaults to "Mar-07".
If we use the format: "Mar-2008" or "Mar 2008", or "3/2008" or "3-2008" it
comes out "Mar-08". Obviously, I could just instruct my staff to use the
latter versions, but it seems silly that we can't force the spreadsheet to
work. Any ideas how to fix this?

 

[Guest]

Custom format as "mmm-yy".

Date entered as Mar-08 appears as Mar-08.

Entered as 01/03/2009 or 01/03/09 appears as Mar-09

HTH

 

[JE McGimpsey]

XL's input parser is independent of cell format (except Text, which
bypasses the parser all together). XL will interpret an entry as a date
in a particular order, if possible - xxx-yy will be interpreted as the
day yy in the month xxx in the current year, regardless of cell format.

So if you need the dates to be dates (rather than text), your staff will
need to enter the full year.

Or you may be able to use a modified entry:

http://cpearson.com/excel/DateTimeEntry.htm

 

[JE McGimpsey]

Custom format as "mmm-yy".

Date entered as Mar-08 appears as Mar-08.

Hmm... doesn't for me. Appears as Mar-07 (XL04).