Months of the World

While putting together the last post, something caught my eye. Something that I’d never noticed about cell formatting.

It was a custom cell format that was created without my action: [$-1409]dddd, d mmmm yyyy
It looked like a date format, but what was that [$-1409] ?!
Recognising 1409 as the New Zealand locale ID, I guessed it had something to do with Regional Settings.

You can actually change your date format for any recognised Country.
Time to play! Fancy your dates in Dutch?

Click your date cell.
From the Format menu, select Cells.
From the Number tab, select Date.
Choose the Locale (location) from the drop down box.

I wanted to see them all at once, so I copied a List of Locale ID (LCID) Values as Assigned by Microsoft, pasted them into Excel and wrote a formula for displaying all of the translations:

Posted in Uncategorized

15 thoughts on “Months of the World

  1. Mystery solved. I’ve seen this many times, wondered about it, and often deleted the part in brackets. I never recognized the hex code (409) for the US locale ID (1033).

    If I set up a date format with [$-409] and send it to someone overseas, this means they’re stuck with the US format, which everyone but the US hates, right?

    Is there an Excel UI equivalent to the named formats, e.g., Format(Now(), “Short Date”), which uses locale-specific formats?

  2. Hi Jon,

    I’m not sure if this is useful or not, but I figure use Application.International(xlYearCode), Application.International(xlMonthCode) and Application.International(xlDayCode), with 4 of each for the xlYearCode and xlMonthCode. I think xlYearCode can be used for some countries where j is used instead of y and 4 x xlMonthCode should decrease/rule out confusion whether months or days are being used as in March 01, instead of 03/01. At least this is what I came up with Remco helping on internationalizing date formats, even if the order is wrong or hyphens are used instead of slashes, it’s still (hopefully) understandable :-)

  3. I tried to reproduce the spreadsheet and must be missing something. I don’t get any variations as are shown in the graphic (all I get are Jan/Feb/Mar for everywhere), and don’t have a Locale dropdown box.

    Is there something else to set/unset?
    …best, Michael

  4. Sweet! This is something I have been thinking about many times, working in a multilingual environment. Thanks for solving this problem.

    By the way, this being my first post here, I just want to say great site!

  5. Rob and Michael,

    I don’t know if this is help, hindrance, not connected or silly, but I noticed date formats work “strangely” with multilanguage packs.

    For example, I was using an “English” version of Excel, but months still showed up as Japanese (My OS is Japanese)

    Ross told me to change my regional settings with the Control Panel and all was solved.

    Given the OS and default regional settings, this probably makes sense, but it also does funny things to the keyboard if I don’t set it back to Japanese when finished.

  6. 1.

    Chinese – Taiwan 0404

    [$-404]e”.”mm”.”dd , it is Taiwan time format

    2.

    Chinese – People’s Republic of China 0804

    “[DBNum2]G/General” displays 1234 as ??????? (These ara Chinese character)

    “[DBNum2][$-804]G/General” displays 1234 as ??????? (These ara Chinese character)

    The difference is the 5th Chinese character, [DBNum2][$-804] shows the correct Traditional Chinese writing for number 3.

  7. Does anyone know what mean [$-F800]? Does it mean default or system culture
    or something like that? Because I couldn’t find such value in the table of
    culture id’s and resulting string depends on my local settings.

    Thanks

  8. Thanks for all this folks – good to know you’re there to look up when I need you. Excellent resource.

    Ben

Leave a Reply

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