Date Separator and Regional Settings

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

Posted in Uncategorized

One thought on “Date Separator and Regional Settings

  1. Hi Rob,

    Good stuff. As a Dutch native I know the issue all to well. Different date formats can be confusing, especially if you have to interact with other software packages, say a SQL database or php script. Regional settings can confusing at times. Another one of those issues is with comma’s and dots in numbers (comma is the decimal seperator in Europe) and even with argument seperators (in Dutch the argument seperator in a worksheet function is a semicolumn (;), yet in VBA it’s a comma.

    Andrew and I have already tackled the calendar date format issue before. If you’re interested have a look at his date utilities from his (open source) tools:

    As for the calendar date format; I’m a fan of the ISO 8601 standard. It displays a calendar date in the format CCYY-MM-DD It also advocates a standard for the week number, another one of those pesky issues.
    If all software would adhere to that standard there would be less confusion. More info:

    Another nice discussion on the date format issue can be found on Dennis his old forum:

    Very interesting read there.



Leave a Reply

Your email address will not be published. Required fields are marked *