DATEDIF Function – Google Sheets

The DATEDIF function calculates the difference between two days. You can return the difference in days, months, years, or combinations. Whereas the DAYS function will only return days, this function has multiple output options.

DATEDIF function screen capture
DATEDIF Function

The input for this function needs to be either surrounded by quotes or a cell reference. 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.

Related functions

DAYS – Simple duration of time formula with just days.

MINUS – Subtract one value from another. You can use this function with dates.

Errors

#NUM – The starting date is after the beginning date.

DATEDIF YouTube Video Tutorial

Purpose

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

Syntax

=DATEDIF(start_date,end_date,unit)

start_date The date at which to start the calculation

end_date The date 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, it starts counting after the end of the previous 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. The MD value can’t go higher than 30.
  • D – Number of whole days elapsed between start and end dates

Examples

Example 1 – Plain and Simple

FormulaResult
=DATEDIF("March 1,2017","March 3, 2017","D")2

This function returns two days, the same as if you did ="3/3/17"-"3/1/17".

Example 2 – Different outputs

DATEDIF example

Above are examples of using DATEDIF 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.

Related Posts

Count cells with dates between two days

Leave a Comment