Date Functions

The following section 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.

SyntaxResult
TODAY()A number representing a date

 

ExampleResult
=TODAY()41059 (30th May 2012)

 


DAY

Returns the day of the month for a provided date.

SyntaxWhereResult
DAY(d)d is a number representing a dateA number

 

ExampleResult
=DAY(TODAY())30

 


WEEKDAY

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

SyntaxWhereResult
WEEKDAY(d)d is a number representing a dateA number

 

ExampleResult
=WEEKDAY(TODAY())4 (Wednesday)

 


MONTH

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

SyntaxWhereResult
MONTH(d)d is a number representing a dateA number

 

ExampleResult
=MONTH(TODAY())5

 


YEAR

Returns the year as a number for a given date.

SyntaxWhereResult
YEAR(d)d is a number representing a dateA number

 

ExampleResult
=YEAR(TODAY())2012

 


DATE

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

SyntaxWhereResult
DATE(year, month, day)year is a number
month is a number
day is a number
A number representing a date

 

ExampleResult
=DATE(2012, 5, 31)41060

 


NOW

Returns a number representing the current time.

SyntaxResult
NOW()A number representing a time

 

ExampleResult
=NOW()0.75 (18:00)

 


TIME

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

SyntaxWhereResult
TIME(hour, minute, second)hour is a number
minute is a number
second is a number
A number representing a time

 

ExampleResult
=TIME(18, 0, 0)0.75

 


 

HOUR

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

SyntaxWhereResult
HOUR(t)t is a number representing a timeA number

 

ExampleResult
=HOUR(NOW())18

 


 

MINUTE

Returns a number representing the minute for the given time.

SyntaxWhereResult
MINUTE(t)t is a number representing a timeA number

 

ExampleResult
=MINUTE(TIME(18, 15, 0))15

 


 

SECOND

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

SyntaxWhereResult
SECOND(t)t is a number representing a timeA number

 

ExampleResult
=SECOND(TIME(18, 0, 17))17

 


 

DAYS

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

SyntaxWhereResult
DAYS(days)days is a numberA number

 

ExampleResult
=TODAY() + DAYS(2)The day after tomorrow

 


 

HOURS

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

SyntaxWhereResult
HOURS(hours)hours is a numberA number

 

ExampleResult
=NOW() + HOURS(2)20:00

 


MINUTES

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

SyntaxWhereResult
MINUTES(minutes)minutes is a numberA number

 

ExampleResult
=NOW() + MINUTES(15)18:15

 


SECONDS

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

SyntaxWhereResult
SECONDS(seconds)seconds is a numberA number

 

ExampleResult
=NOW() + SECONDS(20)18:00:20

DATEFORMAT

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

SyntaxWhereResult
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 specifierDescriptionOutput 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
ExampleResult
 =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