I’m a New Zealander and that’s what I’ll be writing on 7 March for NZ’s Census 2006.
There has been a bit of news in this country about that issue. Until now, you could not record your ethnicity as “New Zealander” in New Zealand! Strange but true. But that’s all about to change.

<shakes head> Back to Excel… where was I?

If you live in a non-US country, you might have been caught by some applications using mm/dd as the default date format.
It has often led to problems for me.
Take the date: 7th of March. It could be written 07/03 (as we do in New Zealand) or 03/07 as it is in the US.

It’s really quite annoying that the first 12 days of the month could be mistaken for dd/mm or mm/dd.
There’s no use crying about it… I just find a way to deal with it.

The way I go is to always format the month as text. In other words dd-mmm-yyyy which appears as 07-Mar-2006
I have done it this way for a long time.
Over the years I’ve noticed an unfortunate problem surfacing. Some computers format my date cells differently!
Instead of the format 07-Mar-2006, it comes out as 07/Mar/2006. Those slashes look out of place and really ugly!

It turns out to be an Excel vs. Regional Settings quirk.

Scenario 1:

You format a cell using the same date separator as that specified in Regional Settings. The date separator will, from then on, always inherit from Regional Settings.

Excel stores the date separator as a forward slash.
eg. Regional Settings = dd-MMM-yyyy then I format a cell as dd-mmm-yyyy. Excel stores the format as dd/mmm/yyyy.

Scenario 2:

You format a cell using a different date separator to that specified in Regional Settings. The cell will then never inherit from Regional Settings.
Internally Excel will prefix the date separator with a hidden backslash. ActiveCell.NumberFormat will not show the hidden blackslash.

Since my computer’s Regional Settings has a hyphen (-) for a date separator, I activated the “inherit” mode for the date separator.

The general rule is, when you want to enforce date separators, prefix them with a backslash.
In my case, I just need to change my cell formats to: dd-mmm-yyyy