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:
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?
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 :-)
Nice one, Rob ! Thanks !
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
Michael,
I’ve tested this on Excel 2002 and Excel 2003. I dont have early versions to test it on.
What version of Excel do you run?
Rob
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!
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.
Rob –
Still running XL2000/Windows 2000 until the lease expires this summer.
…Best, Micahel
Awk – a typo in my own name!
…mrt
Hi,
if you are interested: Here you can download a multilingual calendar using this technique:
multilingual.zip
It was the christmas-special of our leading formular-page in Germany.
Best regards
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.
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
Thanks for all this folks – good to know you’re there to look up when I need you. Excellent resource.
Ben
After all these years I made a Finnish rendering of this idea on Excel last February and today I shared it on Docs.com. :-) https://doc.co/QptkNF