Date Functions
For the examples below, we'll assume its the 30th May 2012, and its 6pm (18:00)
TODAY
|
Form
|
Result
|
|---|---|
|
TODAY()
|
A number representing a date
|
Description
Today's date value
|
Example
|
Result
|
|---|---|
|
=TODAY()
|
41059 (30th May 2012)
|
DAY
|
Form
|
Where
|
Result
|
|---|---|---|
|
DAY(d)
|
d is a number representing a date
|
A number
|
Description
Works out the day of the month for the given date
|
Example
|
Result
|
|---|---|
|
=DAY(TODAY())
|
30
|
WEEKDAY
|
Form
|
Where
|
Result
|
|---|---|---|
|
WEEKDAY(d)
|
d is a number representing a date
|
A number
|
Description
Works out the day of the week for the given date - Sunday is 1.
|
Example
|
Result
|
|---|---|
|
=WEEKDAY(TODAY())
|
4 (Wednesday)
|
MONTH
|
Form
|
Where
|
Result
|
|---|---|---|
|
MONTH(d)
|
d is a number representing a date
|
A number
|
Description
Works out the month for the given date
|
Example
|
Result
|
|---|---|
|
=MONTH(TODAY())
|
5
|
YEAR
|
Form
|
Where
|
Result
|
|---|---|---|
|
YEAR(d)
|
d is a number representing a date
|
A number
|
Description
Works out the year for a given date.
|
Example
|
Result
|
|---|---|
|
=YEAR(TODAY())
|
2012
|
DATE
|
Form
|
Where
|
Result
|
|---|---|---|
|
DATE(year, month, day)
|
year is a number
month is a number day is a number |
A number representing a date
|
Description
Calculates the date value for the given day, month and year.
|
Example
|
Result
|
|---|---|
|
=DATE(2012, 5, 31)
|
41060
|
NOW
|
Form
|
Result
|
|---|---|
|
NOW()
|
A number representing a time
|
Description
The value for the current time.
|
Example
|
Result
|
|---|---|
|
=NOW()
|
0.75 (18:00)
|
TIME
|
Form
|
Where
|
Result
|
|---|---|---|
|
TIME(hour, minute, second)
|
hour is a number
minute is a number second is a number |
A number representing a time
|
Description
Calculates the time value for the given hour, minute and second.
|
Example
|
Result
|
|---|---|
|
=TIME(18, 0, 0)
|
0.75
|
HOUR
|
Form
|
Where
|
Result
|
|---|---|---|
|
HOUR(t)
|
t is a number representing a time
|
A number
|
Description
Works out the hour of the day for the given time.
|
Example
|
Result
|
|---|---|
|
=HOUR(NOW())
|
18
|
MINUTE
|
Form
|
Where
|
Result
|
|---|---|---|
|
MINUTE(t)
|
t is a number representing a time
|
A number
|
Description
Works out the minute of the hour for the given time.
|
Example
|
Result
|
|---|---|
|
=MINUTE(TIME(18, 15, 0))
|
15
|
SECOND
|
Form
|
Where
|
Result
|
|---|---|---|
|
SECOND(t)
|
t is a number representing a time
|
A number
|
Description
Works out the second of the minute for the given time.
|
Example
|
Result
|
|---|---|
|
=SECOND(TIME(18, 0, 17))
|
17
|
DAYS
|
Form
|
Where
|
Result
|
|---|---|---|
|
DAYS(days)
|
days is a number
|
A number
|
Description
Converts days so it can be added to or subtracted from a date.
|
Example
|
Result
|
|---|---|
|
=TODAY() + DAYS(2)
|
The day after tomorrow
|
HOURS
|
Form
|
Where
|
Result
|
|---|---|---|
|
HOURS(hours)
|
hours is a number
|
A number
|
Description
Converts hours so it can be added to or subracted from a time.
|
Example
|
Result
|
|---|---|
|
=NOW() + HOURS(2)
|
20:00
|
MINUTES
|
Form
|
Where
|
Result
|
|---|---|---|
|
MINUTES(minutes)
|
minutes is a number
|
A number
|
Description
Converts minutes so it can be added to or subtracted from a time.
|
Example
|
Result
|
|---|---|
|
=NOW() + MINUTES(15)
|
18:15
|
SECONDS
|
Form
|
Where
|
Result
|
|---|---|---|
|
SECONDS(seconds)
|
seconds is a number
|
A number
|
Description
Converts seconds so it can be added to or subtracted from a time.
|
Example
|
Result
|
|---|---|
|
+NOW() + SECONDS(20)
|
18:00:20
|