Date Functions

The following chapter describes the list of functions available for working with dates along with the associated syntax and examples.

For the examples below, we'll assume its the 30th May 2012, and its 6pm (18:00).

TODAY

Returns a number in days representing the current date from the epoch 31st December 1899.

Syntax	Result
TODAY()	A number representing a date

Example	Result
=TODAY()	41059 (30th May 2012)

DAY

Returns the day of the month for a provided date.

Syntax	Where	Result
DAY(d)	d is a number representing a date	A number

Example	Result
=DAY(TODAY())	30

WEEKDAY

Works out the day of the week for the given date - Sunday is 1.

Syntax	Where	Result
WEEKDAY(d)	d is a number representing a date	A number

Example	Result
=WEEKDAY(TODAY())	4 (Wednesday)

MONTH

Return a number represnting the month for the given date. 1 January, 12 is December.

Syntax	Where	Result
MONTH(d)	d is a number representing a date	A number

Example	Result
=MONTH(TODAY())	5

YEAR

Returns the year as a number for a given date.

Syntax	Where	Result
YEAR(d)	d is a number representing a date	A number

Example	Result
=YEAR(TODAY())	2012

DATE

Calculates the date value for the given day, month and year.

Syntax	Where	Result
DATE(year, month, day)	year is a number month is a number day is a number	A number representing a date

Example	Result
=DATE(2012, 5, 31)	41060

NOW

Returns a number representing the current time.

Syntax	Result
NOW()	A number representing a time

Example	Result
=NOW()	0.75 (18:00)

TIME

Returns a number representing the time for a given hour, minute and second.

Syntax	Where	Result
TIME(hour, minute, second)	hour is a number minute is a number second is a number	A number representing a time

Example	Result
=TIME(18, 0, 0)	0.75

HOUR

Returns a number representing the hour of the day in hours for the given time.

Syntax	Where	Result
HOUR(t)	t is a number representing a time	A number

Example	Result
=HOUR(NOW())	18

MINUTE

Returns a number representing the minute for the given time.

Syntax	Where	Result
MINUTE(t)	t is a number representing a time	A number

Example	Result
=MINUTE(TIME(18, 15, 0))	15

SECOND

Returns a number representing the seconds of the minute for the given time.

Syntax	Where	Result
SECOND(t)	t is a number representing a time	A number

Example	Result
=SECOND(TIME(18, 0, 17))	17

DAYS

Converts days so it can be added to or subtracted from a date.

Syntax	Where	Result
DAYS(days)	days is a number	A number

Example	Result
=TODAY() + DAYS(2)	The day after tomorrow

HOURS

Converts hours so it can be added to or subracted from a time.

Syntax	Where	Result
HOURS(hours)	hours is a number	A number

Example	Result
=NOW() + HOURS(2)	20:00

MINUTES

Converts minutes so it can be added to or subtracted from a time.

Syntax	Where	Result
MINUTES(minutes)	minutes is a number	A number

Example	Result
=NOW() + MINUTES(15)	18:15

SECONDS

Converts seconds so it can be added to or subtracted from a time.

Syntax	Where	Result
SECONDS(seconds)	seconds is a number	A number

Example	Result
=NOW() + SECONDS(20)	18:00:20

DATEFORMAT

Returns a text value that represents a given date in a specified format.

Syntax	Where	Result
DATEFORMATE(date,format)	date is a valid date value format is a string describing how to date should be presented	Text

Syntax

Where

Result

DATEFORMATE(date,format)

date is a valid date value

format is a string describing how to date should be presented

Text

The following table describes each specificer. Note that these are case-sensitive.

Format specifier	Description	Output Example
"d"	The day of the month, from 1 through 31.	17
"dd"	The day of the month, from 01 through 31.	17
"ddd"	The abbreviated name of the day of the week.	Wed
dddd"	The full name of the day of the week.	Wednesday
"M"	The month, from 1 through 12.	7
"MM"	The month, from 01 through 12.	07
"MMM"	The abbreviated name of the month.	Jul
"MMMM"	The full name of the month.	July
"y"	The year, from 0 to 99.	22
"yy"	The year, from 00 to 99.	22
"yyy"	The year, with a minimum of three digits.	2022
"yyyy"	The year as a four-digit number.	2022
"h"	The hour, using a 12-hour clock from 1 to 12.	2
"hh"	The hour, using a 12-hour clock from 01 to 12.	02
"H"	The hour, using a 24-hour clock from 0 to 23.	14
"HH"	The hour, using a 24-hour clock from 00 to 23.	14
"m"	The minute, from 0 through 59.	35
"mm"	The minute, from 00 through 59.	35
"s"	The second, from 0 through 59.	8
"ss"	The second, from 00 through 59.	08
"t"	The first character of the AM/PM designator.	P
"tt"	The AM/PM designator.	PM
"K"	Time zone information.	-01:00
"z"	Hours offset from UTC, with no leading zeros.	-1
"zz"	Hours offset from UTC, with a leading zero for a single-digit value.	-01
"zzz"	Hours and minutes offset from UTC.	-01:00

Example	Result
=DATEFORMAT(NOW(), "dd-MMM-yyyy")	30-May 2012
=DATEFORMAT(NOW(), "ddd-dd MMM yy")	Wed-17 Jul 22
=DATEFORMAT(NOW(), "HH:mm"")	18:00
=DATEFORMAT(NOW(), ""dd/MM/yyyy HH:mm"")	30/05/2012 18:00