J-Walk & Associates, Inc.

Annoying Date Entry Behavior

Just a minor rant about an annoying date entry feature...

When you type a date (without a year) into a cell, Excel displays the date using the "d-mmm" number format. For example, I type 6/12 into cell B3 and this is what I get:

If I type 6-12 or even jun 12, Excel also displays 12-Jun.

As far as I can tell, there is no way to change this behavior. Supposedly, Excel's default date format is determined by the "Short date" setting in the Customize Regional Options dialog in the Windows Control Panel. But if you omit the year when entering a date, this setting is ignored.

If a user enters a date without a year, Excel determines the year based on the Calendar settings in the Customize Regional Options dialog box. If you omit the year, it would be helpful if Excel showed you how it interpreted that date. But it doesn't.

The solution, of course, is to format (or pre-format) the cells with your desired date format.

But why does Excel have such a useless default? I think Excel has always worked this way, but I don't recall very many people complaining about. I guess it's not really a big deal.