Spread Formula Reference
Formula Overview / Functions in a Formula / Categories of Functions / Date and Time Functions
In This Topic
    Date and Time Functions
    In This Topic

    The functions that relate to date-time values and time-span values are:

    DATE DATEDIF DATEVALUE DAY
    DAYS DAYS360 EDATE EOMONTH
    HOUR ISO.CEILING MINUTE MONTH
    NETWORKDAYS NETWORKDAYS.INTL NOW SECOND
    TIME TIMEVALUE TODAY WEEKDAY
    WEEKNUM WORKDAY WORKDAY.INTL YEAR
    YEARFRAC      

    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. In Spread, we generally support 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.

    Return to the list of Categories of Functions.