Note: Excel won’t help you fill out the arguments for DATEDIF like other functions, but it will work when configured correctly.
Time units
The DATEDIF function can calculate the time between a start_date and an end_date in years, months, or days. The time unit is specified with the unit argument, which is supplied as text. The table below summarizes available unit values and the result for each. Time units can be given in upper or lower case (i.e. “ym” is equivalent to “YM”).
Example 1 - Basic usage
In the example shown above, column B contains the date January 1, 2016 and column C contains the date March 1, 2018. In column E:
Example 2 - Difference in days
The DATEDIF function can calculate the difference between dates in days in three different ways: (1) total days, (2) days ignoring years, and (3) days ignoring months and years. The screenshot below shows all three methods, with a start date of June 15, 2015, and an end date of September 15, 2021:
The formulas used for these calculations are as follows: Note that because Excel dates are just large serial numbers, the first formula does not need DATEDIF and could be written as simply the end date minus the start date:
Example 3 - Difference in months
The DATEDIF function can calculate the difference between dates in months in two different ways: (1) total complete months, (2) complete months ignoring years. The screenshot below shows both methods, with a start date of June 15, 2015, and an end date of September 15, 2021:
DATEDIF always rounds months down to the last complete number of months. This means DATEDIF rounds the result down even when it is very close to the next whole month. In addition, DATEDIF may not work as expected when start and end dates are “end of month” dates. This example provides more information and alternatives.
Example 4 - Difference in years
The DATEDIF function can calculate the difference between dates in complete years with just one method, shown below:
Notice in row 6 the difference is almost 6 years, but not quite. Because DATEDIF only calculates complete years, the result is still 5. In row 7 we use the YEARFRAC function to calculate a more accurate result.
Example 5 - Age from birthday
The DATEDIF function can be used together with the TODAY function to calculate current age from a birth date. With a full birth date in A1, the formula is: Read a complete explanation here.
Notes
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
