Skip to main content

Date and Time Functions

Important Note​

The DATE group functions can work with dates written in ISO format (e.g., 2025-01-21). However, by default, they use a different format, commonly referred to as the "Excel date format," which is not the same as ISO format or a timestamp.

How to Convert Dates to ISO Format​

To convert a date into ISO format, use the TEXT function and specify the desired format.

Example:
To convert the current date and time into ISO format, use this formula:

=TEXT(NOW(), "yyyy-mm-ddThh:MM:ss")

DATE​

The DATE function combines three separate values to form a date.

DATE(year,month,day)
Example​
=TEXT(DATE(2008,1,1), "yyyy-mm-dd")
Return Value​
"2008-01-01"

DATEVALUE​

The DATEVALUE function converts a date stored as text into the "Excel date format."

DATEVALUE(date_text)
Example​
=DATEVALUE("1/1/2008")
Return Value​
39448

DAY​

Returns the day of a date (1 to 31).

DAY(date)
Example​
=DAY(DATEVALUE("1/1/2008"))
Return Value​
1

MONTH​

Returns the month of a date (1 to 12).

MONTH(date)
Example​
=MONTH(DATEVALUE("1/1/2008"))
Return Value​
1

YEAR​

Returns the year corresponding to a date (1900–9999).

YEAR(date)
Example​
=YEAR(DATEVALUE("1/1/2008"))
Return Value​
2008

WEEKDAY​

Returns the day of the week corresponding to a date.

WEEKDAY(date, return_type)
Parameters​
ParameterTypeRequiredDefaultDescription
datedatetimeyes-The input date.
return_typeintno1Specifies the numbering system for days of the week.
Return Types​
Return_typeNumbers Returned
1 or omitted1 (Sunday) through 7 (Saturday).
21 (Monday) through 7 (Sunday).
30 (Monday) through 6 (Sunday).
Example​
=WEEKDAY(DATEVALUE("1/1/2008"))
Return Value​
3

WEEKNUM​

Returns the week number of a specific date.

WEEKNUM(date, return_type)
Parameters​
ParameterTypeRequiredDefaultDescription
datedatetimeyes-The input date.
return_typeintno1Determines the start day of the week.
Example​
=WEEKNUM(DATEVALUE("1/1/2008"))
Return Value​
1

TIME​

Returns the decimal number for a particular time.

TIME(hour, minute, second)
Example​
=TIME(23, 54, 19)
Return Value​

Decimal value of the time.

TIMEVALUE​

Returns the decimal number of the time represented by a text string.

TIMEVALUE(time_text)
Example​
=TIMEVALUE("11:54:19 PM")
Return Value​

Decimal value of the time.

SECOND​

Returns the seconds of a time value.

SECOND(time)

MINUTE​

Returns the minutes of a time value.

MINUTE(time)

HOUR​

Returns the hour of a time value.

HOUR(time)

NOW​

Returns the current date and time. Supports date offsets.

=NOW()       // Current datetime
=NOW() + 1   // Now + 1 day
=NOW() - 1   // Now - 1 day

TODAY​

Returns the current date. Supports date offsets.

=TODAY()       // Current date
=TODAY() + 1   // Today + 1 day
=TODAY() - 1   // Today - 1 day

YEARFRAC​

Calculates the fraction of the year represented by the number of whole days between two dates.

YEARFRAC(start_date, end_date, [basis])
Parameters​
ParameterTypeRequiredDefaultDescription
start_datedatetimeyes-A date representing the start date.
end_datedatetimeyes-A date representing the end date.
basisintno0The type of day count basis to use.
Example​
=ROUND(YEARFRAC(DATE(2024,1,1), DATE(2025,9,1), 4))
Return Value​
2

EDATE​

Calculates maturity dates or due dates falling on the same day of the month as the start date.

EDATE(start_date, months)
Example​
=TEXT(EDATE(DATE(2024,1,1), 12), "yyyy-mm-dd")
Return Value​
"2025-01-01"

DATEDIF​

Calculates the number of days, months, or years between two dates.

DATEDIF(start_date, end_date, Unit)
Parameters​
ParameterTypeRequiredDescription
start_datedatetimeyesA date representing the starting date of a given period.
end_datedatetimeyesA date representing the ending date of the period.
UnitstringyesThe type of information to return.
Units​
UnitDescription
"Y"Complete years in the period.
"M"Complete months in the period.
"D"Total days in the period.
Example​
=DATEDIF(DATE(2024,1,1), DATE(2025,1,1), "M")
Return Value​
12