Search code examples
excelnumber-formatting

Excel Number Format: What is "[$-409]"?

i'm automating excel, using the macro system as a guide to what i should do through automation. When i format a column as a date, the macro generated a NumberFormat for the column to be:

[$-409]m/d/yy h:mm AM/PM;@

i'm trying to decipher what this really means. i gather from googling, that the values in the square brackets are a "condition", and that if the condition:

$-409

is met, then it will use the NumberFormat

m/d/yy h:mm AM/PM

if not, it uses the NumberFormat

@

The references i find say that the number format "@" is a Text Placeholder

So my questions are:

  1. What is the conditional $-409 testing? Is it comparing something against -409 (i.e. negative four hundred and nine), and if so, what is the dollar sign it's comparing against?

  2. If the conditional fails, and it resorts to the Text Placeholder "at-sign", what does it show as?

Solution

  • To clarify what others have said:

    The [$-409] is a locale code, given in hexadecimal. Prefixing a date with a certain locale code determines what is shown when you use the various date time format codes. For example using the date

    November 28, 1973 11:28:13 AM

    as an example for the following table:

    Format Code  409 (English United States)  804 (Chinese PRC)
===========  ===========================  =================
m            11                           11
mm           11                           11
mmm          Nov                          十一月
mmmm         November                     十一月
d            27                           27
dd           27                           27
ddd          Mon                          二
dddd         Monday                       星期二
y            73                           73
yy           73                           73
yyy          1973                         1973
yyyy         1973                         1973
AM/PM        AM                           上午

    So in the end the same format code with two different locale identifiers, gives different results:

    [$-409]mmmm dd yyyy  h:mm AM/PM
November 27 1973  11:28 AM


[$-804]mmmm dd yyyy  h:mm AM/PM
十一月 27 1973  11:28 上午

    Since finding a list of locale codes is like pulling teeth, here are some references:

    Language Identifier Constants and Strings (Primary source, archive.is)

    Windows Locale Codes Sorted by Locale (archive.is)

    Windows Locale Codes Sorted by Locale (archive.org, archive.is)