And yet this one [$-409]h:mm:ss\ AM/PM;@ is not and shows AM/PM correctly?
I'm using NPOI to read from a Excel file and extract some useful data for my app. While I was parsing dates, I stumbled across this interesting case.
This date 16:00:00 (4 o'clock in the afternoon) with this format [$-F400]h:mm:ss\ AM/PM is showing as 16:00:00 in excel.
Yet a similar format, [$-409]h:mm:ss\ AM/PM;@, shows 04:00:00 PM, which I believe is correct because of the AM/PM notation at the end of the format string.
My excel version is this one: Microsoft® Office Excel® 2007 (12.0.6665.5003) SP3 MSO (12.0.6662.5000)
And I'm using the latest version of NPOI (got from the source code), which interprets [$-F400]h:mm:ss\ AM/PM as hh:mm:ss tt (which doesn't match the way excel shows it)
I know that [$-XXXX] is the locale, I don't know about ;@.
Why, depending on the circunstances, excel is completely ignoring the AM/PM part?
EDIT: It appears the @ is a text-placeholder for AM/PM it seems. Is the absence of this token forcing excel to show a 24-hour format?
As @pnuts commented, this link indicates that Excel uses a special sequence for system default.
"The cause is, that Excel uses an (undocumented) escape sequence for "system default". This is [$-F800] for date, long system default [$-F400] for time, system default".
NPOI completely ignores locale information, as it's passed as a parameter to the DataFormatter. So, instead of using the system default, it parses the "AM/PM" part and shows a 12-hour format, instead of the 24-hour system default
I've solved my problem by changing the source code of the library to suit my needs in this case.