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:
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?
If the conditional fails, and it resorts to the Text Placeholder "at-sign", what does it show as?
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)