If I format a column with mmm dd hh:mm:ss and set a cell’s value to 0, it is displayed as Jan 00 00:00:00.
This feels like it contradicts Part 1, Section L.2.16.9.1 of the ECMA-376 standard, which Excel purportedly follows.
From the spec:
A date that can be interpreted as a numeric value is a serial value. This is made up of a signed integer
date component and an unsigned fractional time component. Going forward in time, the date
component of a serial value increases by 1 each day. A serial value represents a UTC date and time, and,
as such, has no timezone information.
Three different bases can be used for converting dates into serial values:
- In the 1900 date base system, the lower limit is January 1, -9999 00:00:00, which has serial
value -4346018. The upper-limit is December 31, 9999, 23:59:59, which has serial value
2,958,465.9999884. The base date for this date base system is December 30, 1899, which has a
serial value of 0.- In the 1900 backward compatibility date-base system, the lower limit is January 1, 1900,
00:00:00, which has serial value 1. The upper limit is December 31, 9999, 23:59:59, which has
serial value 2,958,465.9999884. The base date for this date base system is December 31, 1899, which has a serial value of 0.- In the 1904 backward compatibility date-base system, the lower limit is January 1, 1904, 00:00:00, which has serial value 0. The upper limit is December 31, 9999, 23:59:59, which has serial value 2,957,003.9999884. The base date for this date base system is January 1, 1904,
which has a serial value of 0.
From this, I gather that 0 should be rendered as Dec 30, 1899; Dec 31, 1899; or Jan 1, 1904; depending on which of the above systems is chosen (Excel gives some customizability here). Am I missing something here, or does someone have an explanation for why it deviates from the spec?
I am on version 16.61.1, if that matters.
