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

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)

Naaf and Grant Wagner have neatly answered the question about your $-409, so I'll just fill in the missing part:

The '@' after the semicolon tells Excel how to treat the data if you enter a string instead of a valid date. The @ simply means "place any text here, verbatim".

Some quick examples to illustrate the point:

=TEXT("abc","hh:mm;@")
abc

=TEXT("abc","hh:mm;@@")
abcabc

See this article for a detailed description of the text formatting options.

This post explains it. Basically 409 is the locale ID for "English - United States". If you used [$-414], for example, then the date would be formatted for "Norwegian (Bokmål)" instead.

My guess for question (2) is that the raw data would be presented as a string instead of being formatted. A quick test would verify this.

[$-409] does not appear to be a condition. It seems to be a Locale code.

If I format a cell with a Custom format of [$-409]m/d/yy h:mm AM/PM;@, enter a date: 1/1/9, then view the formatting for the cell, I see a Date format of m/d/yy h:mm AM/PM with a Locale (location) of English (United States).

If you change the format to something like [$-439]m/d/yy h:mm AM/PM;@ you'll see the contents of the cell in another language.

I'm not sure about @. It might indicate how to display the date if the correct font or locale is not available.

Here is list of Locale IDs Assigned by Microsoft.

TLDR: What goes between the $ and - in the locale code defines the currency symbol to use in the number format(more specifically nothing for: [$-409]).

Excel is old tech, so when you start digging into the details you'll start to notice some of the "baggage"(a.k.a. tech-debt) that's leftover from a bygone era, and this appears to be one of those things that has not aged very well in the documentation.

Here's the story as best as I was able to piece together:

in section 2.1.378 item b. of MS-OE376 - v20160623 it mentions:

NFPartLocaleID = ASCII-LEFT-SQUARE-BRACKET ASCII-DOLLAR-SIGN 1*UTF16-ANY [ASCII-HYPHEN-MINUS 3*8ASCII-DIGIT-HEXADECIMAL] ASCII-RIGHT-SQUARE-BRACKET

in Part 4 section 3.8.31 of the ECMA-376 (1st Edition / Dec 2006) specification, towards the end there is a table of International Considerations in which the final entry mentions:

next going to the remnants of the MSDN tech doc's to try and find out what is a locale ID, it mentions some insights into what the structure of a 32-bit LCID used to be back when Excel was still a Win32 app:

     [$-409] NFPartLocaleID
 0x00000409  <Language info>
          0          0          0          0          0          4          0          9
       0000       0000       0000       0000       0000       0100       0000       1001
+----------+----------+----------+----------+----------+----------+----------+----------+
                     3                     2                     1                     0  byte
+---------------------------------------------------------------------------------------+
|                                   Locale Identifier                                   |
+--------------------------------+----------+-------------------------------------------+
|            Reserved            | Sort ID  |                Language ID                |
+--------------------------------+----------+---------------------+---------------------+
 31                            20 19      16|   SubLanguage ID    | Primary Language ID |
                                            +---------------------+---------------------+
                                             15                 10 9                   0  bit

finally going to MS-LCID v15.0 in section 2.2 there is a Language ID(2 bytes) table that mentions:

So to me the [$-409] in [$-409]d h:mm:ss would read as en-US with no currency symbol defined that formats 12.34 as 12 8:09:36, whereas [$$-409] 0.00 or [$USD-409] 0.00 which would format it as $ 12.34 and USD 12.34 respectively.