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.

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



DATEDIF(date1, date2, units)
Returns the difference between two dates in the units that you specify.

date1The 'from' date.
date2The 'to' date. This should be greater than or equal to the 'from' date.
unitsThe 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_textThe 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_numberThe 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_dateThe 'from' date.
end_dateThe 'to' date.
methodIndicates 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:

FALSEUS (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).
TRUEEuropean 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_dateThe 'from' date.
monthsThe 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_dateThe 'from' date.
monthsThe 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_numberThe 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_numberThe 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_numberThe 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_dateThe 'from' date.
end_dateThe 'to' date.
holidaysA 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_numberThe 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.

hourThe hour number. Preferably between 0 and 23.
minuteThe minute number. Preferably between 0 and 59.
secondThe 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_textThe 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_numberThe date/time value from which you want the day of the week.
return_typeThe range of values returned and what they represent. The possible values are:

1From 0 to 6, where 0 = Monday and 6 = Sunday.
2From 1 to 7, where 1 = Monday and 7 = Sunday.
3From 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_numberThe date/time value from which you want the week number.
return_typeThe method used to calculate the week number. The possible values are:

0International Standard (ISO) week numbers. Weeks begin on Monday. Week 1 is the first week where 4 or more days are in the specified year.
1Microsoft week numbers. Weeks begin on Sunday. Week 1 is the week that contains January 1st.
2Microsoft 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_dateThe 'from' date.
number_daysThe number of working days to add or subtract.
holidaysA 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_numberThe 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_dateThe 'from' date.
end_dateThe 'to' date.
basisThe method used to represent the number of days in the period versus the number of days in a year. The possible values are:

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

If this parameter is omitted it defaults to 0.