Date and Time Functions

The following date and time functions are provided by PipeDream.

Please refer to the previous section for the correct format and methods to use for entering dates and times.

AGE

AGE(date1, date2)

Works out the difference between two dates in years and months. date1 should be the more recent date, and date2 the earlier date.

For example:

AGE(26.6.1994, 25.8.1982)

returns the age as a number, expressed as 12.10, that is, someone aged 12 years and 10 months.

Use INT() to extract the number of years from the result and 100*(n-INT(n)) to extract the number of months.

DATE

DATE(year, month, day)

Builds a date from individual components.

For example,

DATE(61,3,17)

returns 17.03.1961.

DATEVALUE

DATEVALUE (text)

Converts text to a date.

For example,

DATEVALUE("17.3.61")

returns 17.3.1961.

text can also include a time.

For example,

DATEVALUE("17.3.61 17:15")

returns 17.3.1961 17:15.

DAY

DAY(date)

Returns the days component of date.

For example,

DAY(17.3.61)

returns 17.

DAYNAME

DAYNAME(number|date)

Returns a day name string corresponding to the number or date (where Sunday is day one).

HOUR

HOUR(time)

Returns the hours component of time.

For example,

HOUR(18:33:04)

returns 18.

MINUTE

MINUTE(time)

Returns the minutes component of time.

For example,

MINUTE(18:33:04)

returns 33.

MONTH

MONTH(date)

Returns the months component of date.

For example,

MONTH (2.9.91)

returns 9

MONTHDAYS

MONTHDAYS(date)

Returns the number of days in the month of date.

For example,

MONTHDAYS(1.2.84)

returns 29.

MONTHNAME

MONTHNAME(number|date)

Returns a month name string corresponding to the number or date (where January is month one).

NOW

NOW

Returns today's date and time, in the form 2.9.1991 18:33:04.

SECOND

SECOND(time)

Returns the seconds component of time.

For example,

SECOND(18:33:04)

returns 4.

TIME

TIME(hour, minute, second)

Builds a time from individual components.

For example,

TIME(18,33,04)

returns 18:33:04.

TIMEVALUE

TIMEVALUE(text)

Converts text to a time.

For example,

TIMEVALUE("18:33")

returns 18:33:00.

TODAY

TODAY

Returns today's date, in the form 2.9.1991.

WEEKDAY

WEEKDAY(date)

Returns the weekday of date, where Sunday is 1.

For example,

WEEKDAY(17.3.61)

returns 6 (Friday).

WEEKNUMBER

WEEKNUMBER(date)

Returns the week number corresponding to the date.

YEAR

YEAR(date)

Returns the years component of date.

For example,

YEAR(15.3.1066)

returns 1066.