Date Functions
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 |
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 |