Custom number formats - international (2003)

[ColinBurrows]

If I use the NumberFormat "d-mmm-yyyy", then it works fine for English-speaking regional settings, but in Mexico, say, the "yyyy" component would need to be replaced with "aaaa" in order to work. Is there a way to specify a number format that will work no matter what the regional setting? I've seen [$-409] as a prefix sometimes... Is that how to do it?

TIA

[HansV]

The [$$-409] prefix indicates US currency.

The first two entries in the list of date formats are marked with an asterisk *. These formats will automatically be converted to the system date format of whoever opens the workbook. All other date formats, including custom formats, suffer from the problem you mention. This is one of the features of Excel where support for international use fails, alas.

[ColinBurrows]

Thanks for the quick reply. I had noticed the asterisks and wondered if they were related...
The formatting is actually being applied in VBA, so I'm looking to format a whole column with something like:
Columns("L:L").NumberFormat = "d-mmm-yyyy hh:mm_)"
Do you think it's not possible?

[HansV]

That shouldn't be a problem; with a small number of exceptions, VBA uses US English settings. If you specify a date format the way you do, it should be translated to the local version automatically.