[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

DATE

Syntax:

DATE ( year , month , day )

Description: Computes the serial value for the given date.

Arguments:

Name

Type

Description

year

number

A year, truncated to integer, that together with month and day specifies the date whose serial value is to be computed.

For the 1900 date base system:

If year is in the range 0–1899, inclusive, the year shall be interpreted as year + 1900.

If year is in the range 1900–9999, inclusive, the year shall be interpreted as year.

For the 1904 date base system:

If year is in the range 4–1899, inclusive, the year shall be interpreted as year + 1900.

If year is in the range 1904–9999, inclusive, the year shall be interpreted as year.

month

number

A month, truncated to integer, that together with year and day specifies the date whose serial value is to be computed.

If month is in the range 1–12, the month shall be interpreted as month. If month is less than 1 or greater than 12, the month shall be interpreted as the normalized value (see below) of month, and the year shall be adjusted accordingly.

day

number

A day, truncated to integer, that together with month and year specifies the date whose serial value is to be computed.

If day is in the allowable range of days for the month, the day shall be interpreted as day. If day is less than 1 or greater than the number of days in the given month, the day shall be interpreted as the normalized value (see below) of day, and the year and month shall be adjusted accordingly.

 

Month and/or day normalization occurs when month and/or day is outside the range 1–12 and 1–number-of-days-in-the-given-month, respectively. Specifically, if month is greater than 12, that number of months shall be added to the first month in the year specified. If month is less than 1, the magnitude of that number of months, plus 1, is subtracted from the first month in the year specified. If day is greater than the number of days in the month specified, that number of days is added to the first day in the month specified. If day is less than 1, the magnitude of that number of months, plus 1, is subtracted from the first day in the month specified. If both month and day in the same date are normalized, month is normalized first.

Return Type and Value: number – The serial value for the given date.

However, if

year is less than 0 or is greater-than or equal-to 10000, and the 1900 date base system is being used, #NUM! is returned.

year is less than 4, is greater-than or equal-to 10000, is in the range 1900–1903, inclusive, and the 1904 date base system is being used, #NUM! is returned.

[Example: For the 1900 date base system:

DATE(0,1,1) results in a serial value of 1
DATE(1899,1,1) results in a serial value of 693598
DATE(1900,1,1) results in a serial value of 1
DATE(9999,12,31) results in a serial value of 2958465

For the 1904 date base system:

DATE(4,1,1) results in a serial value of 0
DATE(1899,1,1) results in a serial value of 692136
DATE(1904,1,1) results in a serial value of 0
DATE(9999,12,31) results in a serial value of 2957003

end example]