1. Spreadsheet.com Support
  2. Functions
  3. Date and time functions

DATEDIF

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

Sample Usage

DATEDIF(DATE(1969, 7, 16), DATE(1969, 7, 24), "D") Returns 8, as there is a difference of 8 days between the two dates provided.

DATEDIF(A1, A2, "YM") Would return the number of months between the date values in both cells, subtracting years. For instance, if A1 was 1/1/2000 and A2 was 2/1/2010, this function would return 1, for a difference of 1 month between January and February, ignoring the years.

DATEDIF("7/16/1969", "7/24/1969", "Y") Returns 0 as there are not years between these two dates.

Syntax

DATEDIF(start_date, end_date, unit)

  • start_date - The start date to consider in the calculation. Must be a reference to a cell containing a DATE, a function returning a DATE type, or a number.

  • end_date - The end date to consider in the calculation. Must be a reference to a cell containing a DATE, a function returning a DATE type, or a number.

  • unit - A text abbreviation for unit of time. For example,"M" for month. Accepted values are "Y","M","D" ,"MD","YM","YD".

    • "Y": the number of whole years between start_date and end_date.

    • "M": the number of whole months between start_date and end_date.

    • "D": the number of days between start_date and end_date.

    • "MD": the number of days between start_date and end_date after subtracting whole months.

    • "YM": the number of whole months between start_date and end_date after subtracting whole years.

    • "YD": the number of days between start_date and end_date, assuming start_date and end_date were no more than one year apart.

Notes

  • Months and years are only counted if they are equal to or go past the "day." For example, the function returns "4 months" between the dates 9/30/15 and 2/28/16 (even though the 28th is the last day of the month).

  • If DATEDIF produces a result in an unexpected format, ensure that no pre-existing format has been applied to the cell. For example, if DATEDIF(DATE(1969,7,16),DATE(1969,7,24),"D") returns 1/4/1900, the Date format has been applied to the cell. Change the format of the cell to Number in order to view the expected result of 8.

  • Use unit "MD" to answer questions such as, "After subtracting whole years and whole months from my age, how many days old am I?"

  • Use unit "YM" to answer questions such as, "After subtracting whole years from my age, how many whole months old am I?"

  • Use unit "YD" to answer questions such as, "How many days has it been since my last birthday, given my birthdate and today's date?"