[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

Date Representation

Going forward in time, the date component of a serial value increases by 1 each day.

There are two different bases for serial values:

In the 1900 date base system, the lower limit is January 1, 1900, which has serial value 1. The upper-limit is December 31, 9999, which has serial value 2,958,465.

In the 1904 date base system, the lower limit is January 1, 1904, which has serial value 0. The upper-limit is December 31, 9999, which has serial value 2,957,003.

A serial value outside of the range for its date base system is ill-formed.

As to which date base system an implementation uses by default or whether it allows its users to switch between date base systems, is unspecified. See §3.17.6.7 for XML-related details. [Note: As the XML allows either date base system to be used, an implementation must be able to deal with both systems. end note]

For legacy reasons, an implementation using the 1900 date base system shall treat 1900 as though it was a leap year. [Note: That is, serial value 59 corresponds to February 28, and serial value 61 corresponds to March 1, the next day, allowing the (non-existent) date February 29 to have the serial value 60. end note] A consequence of this is that for dates between January 1 and February 28, WEEKDAY shall return a value for the day immediately prior to the correct day, so that the (non-existent) date February 29 has a day-of-the-week that immediately follows that of February 28, and immediately precedes that of March 1.

[Example: For the 1900 date base system:

DATEVALUE("01-Jan-1900") results in the serial value 1.0000000…
DATEVALUE("03-Feb-1910") results in the serial value 3687.0000000…
DATEVALUE("01-Feb-2006") results in the serial value 38749.0000000…
DATEVALUE("31-Dec-9999") results in the serial value 2958465.0000000…

For the 1904 date base system:

DATEVALUE("01-Jan-1904") results in the serial value 0.0000000…
DATEVALUE("03-Feb-1910") results in the serial value 2225.0000000…
DATEVALUE("01-Feb-2006") results in the serial value 37287.0000000…
DATEVALUE("31-Dec-9999") results in the serial value 2957003.0000000…

end example]