Date and Time Functions

For most of these functions you can specify the date argument as a DateTime object, as in the result of a function such as DATE(2003,7,4), or a TimeSpan object, as in the result of a function such as TIME(12,0,0). For compatibility with Excel, it also allows dates to be specified as a number (as in 37806.5) or as a string (as in "7/4/2003 12:00"). The numbers and strings are converted to instances of the DateTime class.

Dates as numeric values are in the form x.y, where x is the "number of days since December 30, 1899" and y is the fraction of day. Numbers to the left represent the date. Times as numeric values are decimal fractions ranging from 0 to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.).

The following three formulas produce the same result:

YEAR(DATE(2004,8,9))

YEAR(38208)

YEAR("8/9/2004")

In Excel, dates can range from 01/01/1900 to 12/31/9999, and in the .NET framework, instances of the DateTime class can range from 01/01/0001 to 12/31/9999. GcSpreadSheet generally supports the larger range found in the .NET framework. For Excel compatibility there are a few cases where the function allows only the smaller range (for example, the DATE function can only be used to enter dates since 01/01/1900).

You may see some differences in values if exporting to or importing from Excel. Both Excel and OLE automation use doubles to represent dates and times, with the integer portion of the double representing the number of days from a base date. In Excel, the base date that is used is 01/01/1900 and the year 1900 is treated as a leap year. In OLE automation, Microsoft corrected this by using the base date of 12/31/1899. As OLE automation does, our spreadsheets treat 1900 as a non-leap year and thus use the base date of 12/31/1899.