This formula offers an easy way to compare the difference between two dates in days, months, and years, in a way that is a bit more flexible then conventional methods.
Syntax
= DATEDIF(start_date, end_date, units)start_date - any date in Excel that will mark the beginning of a specified period
end_date - any date in Excel that will mark the end of a specified period
Units - the units in which you the formula results to be displayed
Units (put in quotes inside actual formula):
D - # of days in specified period
M - # of full months in specified period
Y - # of full years in specified period
MD - # of days in specified period. Months and years are ignored.
YM - # of full months in specified period, days and years of the period are ignored
YD - # of full days in specified period, years of the period are ignored
Explanation
This function is a little bit odd as you won't be able to use the standard autofill feature that you may be used to. Instead, manually type out "=DATEDIF(" then enter in your start date, end date, and your desired units (units have to be surrounded by quotes).
It is also important to note that this function will only return full/complete days, months and years. This means that DATEDIF won't return partial months and will round down to the nearest whole month. In the example above, although the two dates 1/1/2021 and 3/31/2021 are closer to 3 months apart, the formula will round down to 2. The same thing happens with years.
Examples
Example 1 - Number of Months Between Two Dates:
While it is easy to subtract two dates together to get the number of days between them, there isn't a simple way to calculate the difference in the number of months across years. The incorrect method using MONTH can work for date ranges within a year, but across multiple years the DATEDIF formula shines.
Example 2 - Number of Days Between Two Dates Ignoring Year:
Another good use of this formula is for calculating the difference between two dates while ignoring years. Sometimes you'll find the need to compare the difference between days while your dates range across years. Usually you would just do a simple subtraction across the dates to calculate the number of days, but here that would calculate every day between. Using DATEDIF you can easily ignore the year difference by including "YD" at the end of your formula.