In New Zealand, we format our dates dd/mm/yy. However, the United States format as mm/dd/yy.
When I see a date by itself, like 12/01/09, I ask “is this the 12th of January or the 1st of December”?
When I started working with Oracle RDBMS, a habit I picked up was to format dates dd-mon-yyyy.
This avoided the dd/mm abiguity by supplying a 3 letter abbreviated month: 01-Dec-2009.
I borrowed the format for use in Excel, and I merrily formatted every date I could as dd-mmm-yyyy.
But, one day, I visited a user and saw this:
Shock! How dare Excel change my format to use slashes! It looks just plain weird.
It seems that before storing a date format, Excel does a quick check of the Date Separator in Control Panel Regional Settings (from VBA: Application.International(xlDateSeparator)) and performs character replacement.
So, while I was expecting Excel to store my custom date format as “d d dash m m m dash y y y y”, Excel instead stores “d d dateseparator m m m dateseparator y y y y”.
If that Workbook is then opened on a computer using a Slash as a Date Separator the weird date appears.
The trick, I’ve learned, is to use the escape character backslash to force the dash in: dd-mmm-yyyy
A “gotcha” is that the dd-mmm-yyyy presented as a re-usable item in the Custom Number format list is what is shown. It has no escape characters.
Because you can’t really re-use it without typing it out again, I’ve written a macro to apply the format to my selection.
Selection.NumberFormat = “dd-mmm-yyyy”