Excel 2004: Switches date formats when editing cells

Posted by Pierre Igot in: Microsoft
April 21st, 2006 • 2:15 pm

I have a number of Excel documents in which I have dates entered and displayed in the YYYY-MM-DD format. For example, today’s date would be “2006-04-21.”

When entering a date of a cell using this particular date format, I typically type the date in that same format, i.e. YYYY-MM-DD. So when entering today’s date in a cell, I would type this:

Entering new date in YYYY-MM-DD format

It’s pretty straightforward stuff. The cell is formatted to display the date in this format, and this is the format that I use when typing out the date. So unsurprisingly, after I exit the cell, it looks like this:

Date in YYYY-MM-DD format

But now let’s say I want to edit the date in the cell. I need to make the cell editable again, which I can do either by double-clicking on it with the mouse (which makes the cell editable and places the insertion point in the exact location where the click is), or by pressing control-U (which makes the cell editable and places the insertion point after the last character in the cell).

However, regardless of the method that I use to make the cell editable, here is what I get:

Editing existing date in YYYY-MM-DD format

In other words, Excel completely ignores both the date format applied to the cell (which is YYYY-MM-DD) and the date format that I used when entering the date in the cell, and instead reverts to the default date format for the system, which in my case is DD/MM/YYYY.

Why on earth does it do this? DD/MM/YYYY might be my preferred format for displaying dates in general, but when I am editing dates in a spreadsheet document, I expect to have some flexibility. More important, I expect Excel to follow my choices, and not to impose its default behaviour on me regardless of the date format I’ve chosen for the cells!

This is, I am afraid, a typically example of how Microsoft, in designing its software, unilaterally decides what’s best for the user, and refuses to accommodate even the smallest variation from the norm. For crying out loud, if I have formatted the cell using the YYYY-MM-DD format, and if I have entered the date using the YYYY-MM-DD, then obviously when I want to edit the date I want it to be in the YYYY-MM-DD format!

It might sound like a small detail, but when you are editing dates on a daily basis, it is not! When I want to edit a date in the YYYY-MM-DD format, I most often want to change the day, i.e. the DD part. But of course, as soon as I make the cell editable with control-U, Excel puts the insertion point at the end, but the DD portion of the date is moved to the beginning of the cell, which now uses the DD/MM/YYYY format!


You’d think that, after 11 major revisions, a software title such as Excel should have such details nailed and exhibit behaviours that are both user-friendly and sensible. But no… This is Microsoft we are talking about. Eleven versions later, they still can’t get the basics right.

Comments are closed.

Leave a Reply

Comments are closed.