Types of Date and Time Functions in Google Sheets

There are many types of date and time functions available in Google Sheets. This page groups them into broad categories to understand their purpose. Let’s start by looking at the functions that return helpful information about dates or times.

Videos Covering all Date and Time Functions

Dates and Time Helper Functions

These functions help us use dates or times by returning information from dates or times without changing them.

Help text for the DATE function
Example of a Time Helper Function

DATE

The DATE function accepts values for the year, month, and day and returns them as one date. You can use this function to input date values into other formulas.

DATEVALUE

The DATEVALUE function accepts a date and returns its serial number. The serial number results from the sequential day numbering system that starts with December 30, 1899, as day 0.

TIME

The TIME function accepts values for the hour, minute, and second, then returns them as one time. You can use this function to feed time values into other formulas.

TIMEVALUE

Google Sheets treats time as a fraction of one day. For example, noon is .5. The TIMEVALUE function will show a time as a decimal representing a portion of a day.

Duration Functions

The duration functions calculate the difference between two date values.

Function help for DATEDIF
Example of a Duration Function

DATEDIF

The DATEDIF function finds the number of days, months, or years between two dates.

DAYS360

The DAYS360 function is both a finance function and a date function. It returns the number of days between two days using the 360-day counting convention. Some banks use this convention to calculate interest. See this video for an example of the 360-day method.

DAYS

The DAYS function calculates the difference between two dates. Using DAYS is the same as subtracting one date from another.

NETWORDAYS and NETWORKDAYS.INTL

You can use the NETWORKSDAYS or NETWORKDAYS.INTL functions to calculate the workdays between two dates. The INTL variation allows different weekends than the traditional Saturday and Sunday.

YEARFRAC

If you want to find what fraction of a year has elapsed between two dates, the YEARFRAC function will do this calculation for you.

Functions That Return Parts of Dates and Times

This group of functions extracts parts of information from dates or times.

DAY function help text
Extracting Part of a Date

DAY

Returns a day’s DAY value as a number 1 through 31.

MONTH

Returns a day’s MONTH value as a number 1 through 12.

ISOWEEKNUM

Returns a day’s iso week number value as a number.

WEEKDAY

Returns the day of the week number.

WEEKNUM

Returns a day’s week number value as a number.

YEAR

Returns a day’s YEAR value as a number.

HOUR

Returns a time’s HOUR value as a number.

MINUTE

Returns a time’s MINUTE value as a number.

SECOND

Returns a time’s SECOND value as a number.

Functions That Return Different Dates

The EDATE function and it's help text
A Function that Modifies a Date

EDATE

The EDATE function returns a date n number of days away from a specified date.

EOMONTH

The EOMONTH function returns the end date of a month a specified number of months away from a date.

WORKDAY and WORKDAY.INTL

You can use the WORKDAY or WORKDAY.INTL functions to find the a day a specified number of workdays away. The international version accepts different days for weekends.

Current Date and Time

Help text for the NOW function
Returning the Current Date and Time

NOW

The NOW function returns the current date and time. This value recalculates every time you open the spreadsheet.

TODAY

The TODAY function returns the current date. This value will recalculate every time you open the spreadsheet.

Conclusion

Now you have seen the different types of date functions. Learn more about how to use them.

Leave a Comment