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.

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Debra Dalgleish. Comment posted 06 October, 2008 7:06pm

   Thanks, that bugs me too, especially since I never use that dd-mmm format.
2. By Les. Ferguson. Comment posted 06 October, 2008 11:15pm

   I never noticed this problem.
   Of course since I hail from the U.K. I wouldn't!
   The other half of the world probably thinks this is a really cool feature
   Shalom
3. By Tim. Comment posted 06 October, 2008 11:29pm

   Surely just a small form of retribution from the rest of the world for the usual assumption from that side of the pond that the whole world works the same way as you do .... e.g. feet instead of metres, pounds instead of kg, miles instead of km, gallons instead of litres, color instead of colour, etc, etc, etc.
   
   Most of the world would class Excel's behaviour (note the spelling) here as a 'useful feature'.
   
   Have a nice day.
4. By John Walkenbach. Comment posted 07 October, 2008 5:38am

   The problem (to me) is not that the day appears before the month. It's that the year is not displayed.
5. By Ron de Bruin. Comment posted 10 October, 2008 1:04pm

   Maybe this Add-in will help
   http://www.rondebruin.nl/qde.htm
6. By JP. Comment posted 10 October, 2008 1:56pm

   John,
   
   There's one workaround: Open a blank worksheet and press Ctrl-1, then format the cells with the date format you want. Save it as type Template, filename "book.xlt" in your XLSTART folder. This will be the default workbook when you start Excel.
   
   Now whenever you start Excel or go to File>New and enter a date, it shows up in the format you want.
   
   Not a perfect solution, but one way to simulate the behavior you want.
   
   --JP
7. By aphrodaia. Comment posted 11 November, 2008 4:38pm

   what stinks is if you have a birthday list, for instance, with just month and day and NO year - if the list is maintained over time (years pass, for instance), you cannot sort on the birthday because even though year does not SHOW if you format it to be dd-mm, the year is counted in the sort so anything you input one year, comes before the next, etc.
8. By chip. Comment posted 12 November, 2008 9:36am

   aphrodaia, simplest solution would be to put columns with =MONTH and =DAY formulas next to your birth dates and sort based on those. Or you could "standardize" your dates and always enter MM/DD/2000, say, assuming you do not know the year.
9. By dodgester. Comment posted 13 October, 2009 11:54am

   I really hate this date format of Excel's behavior and I find very few people using it compared to most date formats. The format I find most people using is the m/d/yy, but I also hate that format. The format I use most often so as I can really see what century it's in as well as get a good idea of what the width is going to be without having to be second guessing is mm/dd/yyyy.
   
   I have seen the cell format method to put into a template for creating new workbooks, but then you lose the general format setting for other formats. I have also seen the macro method as well within the "ThisWorkbook" Class Module, but I don't like that idea either as it means extra processing time per time something is changed in Excel by default.
10. By Bob Jumpman. Comment posted 18 May, 2011 6:35pm

    My problem is similar to aphrodaia's mentioned above. I would like to use month/day without the year.
    
    
    I want to find the average 1st date that an event occurs. Suppose that I see my first American Robin of the year on March 10 2011 and the folowing year on March 12 2012. The average 1st date would be, of course March 11th. However, when I enter the dates into Excel as 3/10 and 3/12, Excel automatically asssumes that they are for the current year. Using the averaging function, Excel gives me an average date of November 04 2011. Not what I want at all!
    
    Here, I am trying to average 12 years worth of date, not just two, and there are 100's of species to record, so averaging this manually is quite a chore.
    
    Can anyone see a solution? Thanks, Bob.
11. By John Walkenbach. Comment posted 18 May, 2011 7:10pm

    The solution is to enter the year along with the month and day. Like this:
    
    3/10/2011
    
    Do you really expect Excel to figure out what year you're entering if you don't tell it?
12. By M. Simms. Comment posted 19 May, 2011 9:47am

    Yes. CDate() does just that when the year is missing from the character string.
13. By Philip Last. Comment posted 26 May, 2011 3:18am

    Regarding the average 1st date, you could convert the date to the number of elapsed days since the 1st day of the year and average that result? That would then give you an average you could add to the first date of the year to get the average date you expect something. If x is the date you saw the first robin then Days = Value("01/01/" & YEAR(x))- x
    
    Averaging this will give you the day the event has happened over time. To see what day that equates to this year then INT(AverageDays) + DateValue("01/01/" & YEAR(today())