SpreadCE Help

Date/time functions

[ ] indicates optional parameters

DATE(year, month, day)
DATEDIF(date1, date2, units)
DATEVALUE(date_text)
DAY(serial_number)
DAYS360(start_date, end_date, [method])
EDATE(start_date, months)
EOMONTH(start_date, months)
HOUR(serial_number)
MINUTE(serial_number)
MONTH(serial_number)
NETWORKDAYS(start_date, end_date, [holidays])
NOW()
SECOND(serial_number)
TIME(hour, minute, second)
TIMEVALUE(time_text)
TODAY()
WEEKDAY(serial_number, [return_type])
WEEKNUM(serial_number, [return_type])
WORKDAY(start_date, number_days, [holidays])
YEAR(serial_number)
YEARFRAC(start_date, end_date, [basis])

Date/time functions

DATE(year, month, day)

Returns the date value corresponding to a specified year, month and day. Note that only dates from 1900 onwards can be used.

year | The year number. If you enter a number less than 1900 then 1900 will be added to the value.
month | The month number. Preferably between 1 and 12.
day | The day number. Preferably between 1 and 31.

DATEDIF(date1, date2, units)

Returns the difference between two dates in the units that you specify.

date1 | The 'from' date.
date2 | The 'to' date. This should be greater than or equal to the 'from' date.
units | The units in which you want the difference expressed. The possible values are:

"y" | The difference in years.
"m" | The difference in months.
"d" | The difference in days.
"ym" | The difference in months, dropping any whole years (the result will always be between 0 and 11).
"yd" | The difference in days, dropping any whole years (the result will always be between 0 and 365).
"md" | The difference in days, dropping any whole months (the result will always be between 0 and 30).

DATEVALUE(date_text)

Returns the date value that corresponds to a date in text form. Note that only dates from 1900 onwards can be used. You should not need to use this function, as dates are automatically converted from text to date values when they are used in formulas.

date_text | The date that you want to convert. It can contain date separators and/or a month name. If only the day and month is supplied then the year will default to the current year.

DAY(serial_number)

Returns the day number (between 1 and 31) from the specified date value.

serial_number | The date value from which you want to extract the day number.

DAYS360(start_date, end_date, [method])

Returns the number of days between two dates using a 360-day year (30 day months).

start_date | The 'from' date.
end_date | The 'to' date.
method | Indicates how to deal with end dates that are the 31st of the month (the start date will always be moved from the 31st to the 30th). The options are:

FALSE | US (NASD) method (if start date is less than the 30th then set end date to 1st of next month, else set end date to 30th).
TRUE | European method (end date moved form 31st to 30th).

| If this parameter is omitted it defaults to FALSE.

EDATE(start_date, months)

Returns the date that is the start date plus or minus a number of months.

start_date | The 'from' date.
months | The number of months to be added or subtracted.

EOMONTH(start_date, months)

Returns the date that is the last day of the month calculated from the start date plus or minus a number of months.

start_date | The 'from' date.
months | The number of months to be added or subtracted.

HOUR(serial_number)

Returns the hour number (between 0 and 23) from the specified date/time value.

serial_number | The date/time value from which you want to extract the hour number.

MINUTE(serial_number)

Returns the minute number (between 0 and 59) from the specified date/time value.

serial_number | The date/time value from which you want to extract the minute number.

MONTH(serial_number)

Returns the month number (between 1 and 12) from the specified date/time value.

serial_number | The date/time value from which you want to extract the month number.

NETWORKDAYS(start_date, end_date, [holidays])

Returns the number of working days (weekdays) between the start and end dates, excluding any holidays.

start_date | The 'from' date.
end_date | The 'to' date.
holidays | A date or array of dates or a reference to a list of dates that are to be treated as non-working days.

NOW()

Returns the serial number corresponding to the current date and time.

SECOND(serial_number)

Returns the second number (between 0 and 59) from the specified date/time value.

serial_number | The date/time value from which you want to extract the second number.

TIME(hour, minute, second)

Returns the serial number corresponding to a specified hour, minute and second.

hour | The hour number. Preferably between 0 and 23.
minute | The minute number. Preferably between 0 and 59.
second | The second number. Preferably between 0 and 59.

TIMEVALUE(time_text)

Returns the time value that corresponds to a time in text form. You should not need to use this function, as times are automatically converted from text to time values when they are used in formulas.

time_text | The time that you want to convert. It can contain time separators and/or AM/PM designators. If seconds or minutes and seconds are not specified then they will default to zero.

TODAY()

Returns the serial number corresponding to the current date.

WEEKDAY(serial_number, [return_type])

Returns a number representing the day of the week of a given date.

serial_number | The date/time value from which you want the day of the week.
return_type | The range of values returned and what they represent. The possible values are:

1 | From 0 to 6, where 0 = Monday and 6 = Sunday.
2 | From 1 to 7, where 1 = Monday and 7 = Sunday.
3 | From 1 to 7, where 1 = Sunday and 7 = Saturday.

| If this parameter is omitted it defaults to 3.

WEEKNUM(serial_number, [return_type])

Returns the week of the year of a given date.

serial_number | The date/time value from which you want the week number.
return_type | The method used to calculate the week number. The possible values are:

0 | International Standard (ISO) week numbers. Weeks begin on Monday. Week 1 is the first week where 4 or more days are in the specified year.
1 | Microsoft week numbers. Weeks begin on Sunday. Week 1 is the week that contains January 1st.
2 | Microsoft week numbers. Weeks begin on Monday. Week 1 is the week that contains January 1st.

| If this parameter is omitted it defaults to 1.

WORKDAY(start_date, number_days, [holidays])

Returns the working day that is the start date plus or minus a number of working days (weekdays), excluding any holidays.

start_date | The 'from' date.
number_days | The number of working days to add or subtract.
holidays | A date or array of dates or a reference to a list of dates that are to be treated as non-working days.

YEAR(serial_number)

Returns the year number (greater than or equal to 1900) from the specified date/time value.

serial_number | The date/time value from which you want to extract the year number.

YEARFRAC(start_date, end_date, [basis])

Returns the fraction of a year represented by the difference between two dates.

start_date | The 'from' date.
end_date | The 'to' date.
basis | The method used to represent the number of days in the period versus the number of days in a year. The possible values are:

0 | US (NASD) 30/360
1 | Actual/actual
2 | Actual/360
3 | Actual/365
4 | European 30/360

| If this parameter is omitted it defaults to 0.