The DATEDIF function calculates the difference between two days. The difference can be returned in day, month, year or a combination thereof. Whereas the DAYS function will only return days, this function has multiple output options.
The input for this function needs to be either surrounded by quotes or a reference to another cell with a date in it. If your days, months, and years are in different cells, use the DATE function to combine them. If you want to know the current age, use TODAY() as the end date.
DAYS – Simple duration of time formula with just days.
MINUS – Subtract one value from another. Can be done with dates.
#NUM – The starting date is after the beginning date.
The DATEDIF function returns the number of day, month, or years between two dates.
start_date Date at which to start the calculation
end_date Date at which to end the calculation
unit Type of output. Choices are “Y”, “M”, “D”, “YM”, “YD”, or “MD”.
Y – Number of whole years elapsed between start and end dates
YM – Number of months elapsed after the number of years shown with the “Y” unit. Will not exceed 11.
YD – Number of days elapsed after the number of years shown with the “Y” unit. No matter how many days after the last year, starts counting after end of last full year and is never over 364.
M – Number of whole months elapsed between start and end dates
MD – Number of days elapsed after the number of months shown with the “M” or “YM” unit. Can’t go higher than 30.
D – Number of whole days elapsed between start and end dates
Example 1 – Plain and Simple
=DATEDIF(“March 1,2017″,”March 3, 2017″,”D”)
This function returns two days, the same as if you did ="3/3/17"-"3/1/17".
Example 2 – Different outputs
Examples showing DATEDIF being used with different parameters.
Live examples in Sheets
Go to this spreadsheet for the examples of the DATEDIF function shown above that you can study and use anywhere you would like.
The TIMEVALUE function converts a time to a fraction of 1. Google Sheets works with times as fractions of a number, not as text strings. This function converts times into their usable form. Noon would be .5 and 6 PM would be .75.
The function can be used with 12 hour AM/PM time or with 24 hour time. If a date and time is given to the function, the date will be ignored, but the time will be converted. To represent seconds, use a second colon. 5 minutes and 12 seconds would be “00:05:12” not “05:12”.
Remember that dates are really just saved as numbers by your spreadsheet with date formatting applied. The date must be in a valid format, so you have to use the DATE function, wrap it in quotes, or use a cell reference to a cell containing a date.
time_text Text representation of a time.
DATEVALUE – Converts a date to a numeric value in a similar way as TIMEVALUE.
The WORKDAY function calculates a workday a given number of days away from a date. You can optionally specify a list of holidays to exclude from the count. If your days, months, and years are in different cells, use the DATE function to combine them.
start_date – The date from which to start counting the days.
days – The number of days to add to the start_date
[holidays] – Optional specification of which days are to be excluded from the count of work days.
WEEKDAY – Returns the week number for a given date.