Date and Time Functions

DATE

Returns the number of ticks representing a particular date in the server’s local timezone. This function should be used to represent a date to other functions instead of representing a date as text, as text dates can lead to problems.

The Year argument can be one to four digits between 1 and 9999.

Month is a number representing the month of the year between 1 and 12.

Day is a number representing the day of the month between 1 and the number of days in the month specified.

Values given outside any of these parameters' legal ranges will return an error.

Example: YEAR(DATE(2008,7,4))* - returns the *Gregorian calendar year of the date, July 4, 2008, produced by this function (2008).

DATEVALUE

Returns the number of ticks of the date represented in text.

Use DATEVALUE to convert a date represented in text ( i.e., MM/DD/YYYY) into ticks that can be passed to other functions.

Date_text is some text representing a date. For example, "30-Jan-2008" or "1/30/2008" are quoted text strings representing dates. The day can only appear before the month in Date_text if month is spelled out ( i.e., "Jan," in the first format) to avoid an ambiguity that can arise in the second format. DATEVALUE returns the #VALUE! error value if Date_text appears out of range.

Any time information in the Date_text is ignored. The ticks returned always represent a time-of-day of Midnight (in the server’s local time).

If the year portion of Date_text is omitted, DATEVALUE uses the current year on the server.

Example=MONTH(DATEVALUE("15-Apr-2008")) - returns the month number of April 15, 2008 (4).

TIME

Returns a time value in ticks for a particular time based on its number of hours, minutes and seconds.

Use TIME to create durations and time periods to be added to other date/time values also represented in ticks.

Hour is a number from 0 (zero) to 23 representing the hour. Any value greater than 12 is interpreted as being in the afternoon.

Minute is a number from 0 to 59 representing minutes past the hour.

Second is a number from 0 to 59 representing seconds past the minute.

When these arguments are outside of their range a #VALUE! error is returned.

Example=SECOND(TIME(14,50,5)) - returns the seconds portion of the time value (5).

TIMEVALUE

Returns a number of ticks representing the time portion of a text string representing the time.

Time values can serve as a duration that can be added to other time or date/time values also represented in ticks.

Time_text is a text string within quotation marks that represents a time.

Examples of acceptable formats include "5:55 PM" and "17:55", if AM/PM is not present AM is assumed (PM times can be entered based on a 24-hour clock, i.e., 5:00 PM is 17:00). Specification of seconds is optional. A time separator is mandatory ("17:00" is acceptable, "1700" is not). When specifying AM or PM, do not use periods ("A.M." or "P.M." will return an error).

Any Date information in time_text is ignored. Time values are a portion of a date/time value represented in ticks. When the return value from TIMEVALUE is treated as a date, it is based off of the minimum date, January 1st, 1 AD.

Example=HOUR(TIMEVALUE("17:00")) - returns the hours portion of the time’s text representation (17).

DATEADD

Returns a Date value containing a date and time value in ticks to which a specified time interval has been added.

The Interval is a String expression representing the time interval you want to add. Its possible values include "yyyy" (year), "y" (days), "d" (days), "w" (weeks), "m" (months), "h" (hours), "n" (minutes), "s" (seconds), "ww" (weeks of year), "q" (quarters).

The Number is a real number representing how many intervals are to be added. Number can be positive (to get date/time values in the future) or negative (to get date/time values in the past). It can contain a fractional part when Interval specifies hours, minutes, or seconds. For other values of Interval, any fractional part of Number is ignored.

The DateValue is a Date. An expression representing the date and time to which the interval is to be added.

Example=DATEADD("h",1.5,NOW( )) - returns the ticks representing a date and time 1 1/2 hours from now.

DATEDIFF

Returns a whole number representing the number of time intervals between two date values represented in ticks.

The Interval is a String expression representing the time interval you want as the unit of difference between two dates. Its possible values include "yyyy" (year), "y" (days), "d" (days), "w" (weeks), "m" (months), "h" (hours), "n" (minutes), "s" (seconds), "ww" (weeks of year), "q" (quarters).

The Date1, Date2 arguments are two date/time values you want to use in the calculation. The value of Date1 is subtracted from the value of Date2 to produce the difference.

The DayOfWeek is an optional value chosen from an enumeration specifying the first day of the week ranging from 1 (Sunday) to 7 (Saturday). If unspecified, Sunday (1) is used. If DayOfWeek is 0, then the first day of week configured on the server’s local settings is used.The WeekOfYear is another optional value chosen from an enumeration with values of January 1st (1), the week having at least four days (2), or the first full week (3). If unspecified, January 1st is used. If WeekOfYear is 0, then the week of year configured on the server’s local settings is used.

Example=DATEDIFF("m",DATE(2004,1,1),DATE(2005,3,1)) - returns the number of months between January, 2004, and March, 2005 (14).

DAYS360

Returns the number of days between two dates based on a 360-day year (twelve 30-day months) used in some accounting applications.

Use this function when computing payments if your accounting system is based on twelve 30-day months.

Start_date and end_date are the two dates between which you want to know the number of days (based on a 360-day year). If start_date occurs after end_date, DAYS360 will return a negative number. Dates should be entered by using the DATE function, or received as results from other formulas and functions.

Method is an optional argument indicating whether to use the European method of computation instead of the American (U.S. NASD) method. Each produces slightly different answers when either Start_date or End_date occurs on the 31st day of a month. The default Method is U.S. NASD (") when omitted.

Any time component of these dates is interpreted in the local time zone of the server.

Example=DAYS360(DATE(2005,1,1),DATE(2005,1,31),"TRUE") - calculates the number of days on an accounting calendar having 30-day months between January 1, 2005 and January 31, 2005 using the European method which treats January 31st as January 30th (29). Using the American method would produce a different answer (30).

YEAR

Returns the year portion of a date as a whole number, ranging from 1 to 9999.

Serial_number is a number of ticks representing a date/time value. Dates should be entered by using the DATE function, or received as results of other formulas and functions. For example, use DATE(2007,2,14) for the 14th day of February, 2007. Problems can occur if dates are entered as text.

Values returned by the YEAR, MONTH and DAY functions will be Gregorian Calendar values regardless of the display format for the supplied date value.

Involving date/time values exceeding the year 9999 in calculations results in an error.

Example=YEAR(DATEVALUE("2/14/2007")) - returns the year portion of the date (2007).

MONTH

Returns the month portion of a date as a whole number, ranging from 1 (January) to 12 (December).

Serial_number is a number of ticks representing a date/time value. Dates should be entered by using the DATE function, or received as results of other formulas and functions. For example, use DATE(2008,4,15) for the 15th day of April, 2008. Problems can occur if dates are entered as text.

Values returned by the YEAR, MONTH and DAY functions will be Gregorian Calendar values regardless of the display format for the supplied date value.

Example=MONTH(DATEVALUE("15-Apr-2008")) - returns the month portion of the date (4).

DAY

Returns the day portion of a date as a whole number.

Serial_number is a number of ticks representing a date/time value. Dates should be entered by using the DATE function, or received as results of other formulas and functions. For example, use DATE(2006,6,22) for the 22nd day of June, 2006. Problems can occur if dates are entered as text.

Values returned by the YEAR, MONTH and DAY functions will be Gregorian Calendar values regardless of the display format for the supplied date value.

Example=DAY(DATE(2006,6,22)) - returns the day portion of June 22, 2006 (22).

TODAY

Returns the ticks representing today’s date, in server local time. The returned date will have no time component ( i.e., midnight server local time).

Date/time values are stored as sequential counts of ticks, so they can be used in calculations.

See the NOW( ) function to get today’s date with its time component.

Example=HOUR(TODAY( )) - returns midnight because this function returns a date value with no time component (0).

HOUR

Returns the hour of a time value. The hour is given as a whole number (fractions rounded down), ranging from 0 (12:00 AM) to 23 (11:00 PM). Hours past noon are always returned based on a 24-hour clock.

Serial_number is a date/time in ticks containing the hour you want to find. Time values are represented in ticks as a portion of a date/time value.

Times may be entered as text strings within quotation marks (for example, "5:55 PM") or received from other formulas and functions (for example, TIMEVALUE("5:55 PM") ).

Example=HOUR("2:50:05PM") - returns the hour portion of the time (14).

MINUTE

Returns the minutes of a time value. The minute is given as a whole number (fractions rounded down), ranging from 0 to 59.

Serial_number is a date/time in ticks containing minutes you want to find. Times values are represented in ticks as a portion of a date/time value.

Times may be entered as text strings within quotation marks (for example, "5:55 PM"), or received from other formulas and functions (for example, TIMEVALUE("5:55 PM") ).

Example=MINUTE("2:50:05PM") - returns the minute portion of the time (50).

SECOND

Returns the seconds of a time value. The second is given as a whole number (fractions rounded down), ranging from 0 to 59. When a time omits seconds, 0 (zero) is assumed.

Serial_number is a date/time in ticks containing seconds you want to find. Times values are represented in ticks as a portion of a date/time value.

Times may be entered as text strings within quotation marks (for example, "5:55 PM"), or received from other formulas and functions (for example, TIMEVALUE("5:55 PM") ).

Example=SECOND("2:50PM") - returns the seconds portion of the time (0).

NOW

Returns the ticks representing today’s date and time (in server local time).

Date/time values are stored as sequential counts of ticks, so they can be used in calculations.

See the TODAY( ) function to get today’s date without its time component.

Example=HOUR(NOW( )) - returns the current hour in local server time.