Date and Time Functions

PipeDream has a set of date and time functions which enable you to build dates and times in number cells and do calculations with them.

You can enter dates and times in one of three ways:

AGE

Syntax:

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.

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

Syntax:

DATE(year, month, day)

Builds a date from individual components.

Example:

DATE(61,3,17)

returns 17.03.1961.

DATEVALUE

Syntax:

DATEVALUE(text)

Converts text to a date.

Example:

DATEVALUE("17.3.61")

returns 17.3.1961.

text can also include a time.

Example:

DATEVALUE("17.3.61 17:15")

returns 17.3.1961 17:15.

DAY

Syntax:

DAY(date)

Returns the days component of date.

Example:

DAY(17.3.61)

returns 17.

DAYNAME

Syntax:

DAYNAME(number|date)

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

HOUR

Syntax:

HOUR(time)

Returns the hours component of time.

Example:

HOUR(18:33:04)

returns 18.

MINUTE

Syntax:

MINUTE(time)

Returns the minutes component of time.

Example:

MINUTE(18:33:04)

returns 33.

MONTH

Syntax:

MONTH(date)

Returns the months component of date.

Example:

MONTH(2.9.91)

returns 9

MONTHDAYS

Syntax:

MONTHDAYS(date)

Returns the number of days in the month of date.

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

Syntax:

NOW

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

SECOND

Syntax:

SECOND(time)

Returns the seconds component of time.

Example:

SECOND(18:33:04)

returns 4.

TIME

Syntax:

TIME(hour, minute, second)

Builds a time from individual components.

Example:

TIME(18,33,04)

returns 18:33:04.

TIMEVALUE

Syntax:

TIMEVALUE(text)

Converts text to a time.

Example:

TIMEVALUE("18:33")

returns 18:33:00.

TODAY

Syntax:

TODAY

Returns today’s date, in the form 2.9.1991.

WEEKDAY

Syntax:

WEEKDAY(date)

Returns the weekday of date, where Sunday is 1.

Example:

WEEKDAY(17.3.61)

returns 6 (Friday).

WEEKNUMBER

WEEKNUMBER(date)

Returns the week number corresponding to the date.

Revisions:

This function was added in 4.50/06.

YEAR

Syntax:

YEAR(date)

Returns the years component of date.

Example:

YEAR(15.3.1066)

returns 1066.