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.
Contents
Dates and Time Helper Functions
These functions help us use dates or times by returning information from dates or times without changing them.
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.
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.
TIMEDIF (Add-On)
Google Sheets does not have a built-in function to calculate the duration between two times on different days. The reason is that Google Sheets treats dates and times as numbers, and subtracting two numbers that represent time different days will result in a decimal value that is not a valid duration. To find the duration between two times on different days, you need to use a formula that combines the DATE, TIME and DATEDIF functions. As an easier alternative, you can install and use the TIMEDIF add-on which will find the duration for you.
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
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
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
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.
Videos Covering all Date and Time Functions
