Calculates the number of days, months, or years between two dates. This function is only provided for compatibility with older Lotus 1-2-3 workbooks. It is a hidden function and will not appear in Excel’s autocomplete.
The syntax of the function is the following:
DATEDIF(start_date, end_date, unit)
start_date: The start date of the period’s length we wish to calculate.
end_date: The end date of the period’s length we wish to calculate.
unit: The type of difference we want to calculate.
The unit argument takes the following values:
| Unit | Returns |
| “Y” | The number of years in the period |
| “M” | The number of months in the period |
| “D” | The number of days in the period |
| “MD” | The difference in days between start_date and end_date. The months and years are ignored |
| “YM” | The difference in months between start_date and end_date. The days and years are ignored |
| “YD” | The difference in days between start_date and end_date. The years are ignored |
If the start_date is greater than the end_date the #NUM! error is returned.
Click on the button to practice using this function, with the help of our Online Assessment Tool:
Here is an example of how to use the DATEDIF function:
In the cell D1 enter your birth date.
In the cell D3 calculate the difference in days between the date in D1 cell and today.
In the cell D4 calculate the difference in days, ignoring the months, between the date in D1 cell and today.
Use DATEDIF (hidden) function, in order to solve this exercise.