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”
End Sub
Hi Rob
FYI
The format on a Dutch system changed the y(Year) to a J (jaar)
so it works OK If you run the code on a Non English system
But I hope one day all people will use the ISO date format yyyy-mm-dd
http://www.rondebruin.nl/isodate.htm
Hi Ron
Just as Excel knows to replace the dashes with date separator placeholders, Excel knows how to replace “y” with a year placeholder.
So, if you Format a date as dd-mmm-yyyy in an English region, and load the Workbook in a Dutch region, the date format changes to dd-mmm-jjjj.
Also, I just noticed this post is a duplicate. I posted this very same topic almost 4 years ago… my memory fails me.
Another thing to be aware of:
If you format a date and that format happens to be the same as Excel’s Short Date Format or Long Date Format, Excel remembers that fact and not your format string. If the file is then opened on another system with different settings for Short or Long Date, you get different presentations too.
Nice solution, Rob! I’ll see if I can have Excel break it.:-)
Niek Otten
@Ron, I agree about using the ISO system too. When I worked in an logistics context, we delivered to a variety of countries around the globe. I would send customers an ISO Calendar to refer to, then use ISO week numbers and dates so there was never any misunderstanding about when shipments would leave or arrive.
It took me over a year to train a client to type the date in different file versions as yyyymmdd (i.e., ISO). It was a PITA because sorting by name gave you the list out of chronological order
If you want this to be a separate format from dd-mmm-yyyy, don’t use backslashes. Use the format
dd”-“mmm”-“yyyy
Excel retains the double quoted substrings.
IMO, as long as you’re storing users’ date entries as date serial numbers rather than as text, it should be entirely up to those users to decide the format in which they want the dates to appear. If you need to write those entries to other systems, use a separate worksheet with formulas to pull in entry data and format that worksheet as the target systems need the dates. Dictating entry formats to users is usually either an exercise in futility or a very effective way to piss them off so much they’d be very unlikely to give you the benefit of the doubt for any other controversial design choices.
If ambiguity is the problem, then ISO formats are best, certainly better than any format in which year appears last.
fzz: Yes, that format is a really good suggestion. I think I may use that.
“Dictating entry formats to users is usually either an exercise in futility or a very effective way to piss them off so much they’d be very unlikely to give you the benefit of the doubt for any other controversial design choices.”
I too believe entry formats should be determined by the user. It creates so much developer-user goodwill when a user goes to take a logical shortcut, and it works for them first time.
However, I think reporting formats are best determined by a designer, for the sake of consistency.
My point is that there should be a separation between user entries and reporting. User entries should be in the user’s preferred date format. Reporting dates should be in the intended format. Passing user entries straight to the printer or other software without data validation and reformatting is begging for problems. A little redundancy is a useful thing.