What is Excel DATEDIF Function?
Excel DATEDIF function is one of the undocumented functions in excel. This means that it is a hidden function in Excel.
When you type a formula name (=DATE) in Excel, the DATEDIF function does not come up in the prompt list (IntelliSense) of function names that Excel displays and even when using the DATEDIF function, Excel will not fill in the arguments like the other functions.
Therefore, you need to know and understand the syntax of this function thoroughly.
Yet, Excel DATEDIF function remains a very useful function for specific problems.
How Excel DATEDIF Function Works?
Excel DATEDIF function calculates the number of days or months or years between two specified dates. The DATEDIF function calculates the difference between two date values and returns a number that represents the specified unit type.
- start_date: This is the initial date of the period. It can be entered as a serial number, or text strings in double quotes, or as a date value result from other formulas.
- end_date: This is a date value that represents the last or ending date of the period. It can be entered as a serial number, or as text strings in double quotes, or as a date value result from other formulas.
- unit: This indicates the time unit we want the information, either in days, months or years. It determines the type of result you get from this function.
There are six available unit values from the DATEDIF function and each give a different output. The units can be entered in upper or lower case (i.e. “y” or “Y”).
When Excel DATEDIF Function is Used?
Excel DATEDIF function is used when you want to calculate the number of days, months or years between two specific dates.
A good example is when you want to calculate the current age of employees from a birth date or current date. Also, if you want to calculate the number of days outstanding debts have been due.
- Excel does not display or fill in the DATEIF function like other functions when typing it in a cell. You need to know the arguments to use. However, the function is available in all versions of Excel.
- You can enter the dates as text strings (“20/5/2020”), or as serial numbers (43971 which represents 20th May, 2020), or as the results of other formulas.
- Microsoft recommends not using the “MD” unit value because it result in a zero, a negative number or inaccurate result.
How to Use Excel DATEDIF Function: Formulas & Examples
There are different real-life scenarios in which you can use Excel DATEIF function.
For example, if you want to calculate the number of days your debtors are required to pay up their debts as at today’s date, from the date the payment was initially due, then you use Excel DATEDIF function.
You can also use DATEDIF function to calculate and set up an anniversary due date for your client’s list.
Now, let us look at some examples to understand how to use the DATEDIF function.
Example 1: Basic Excel DATEDIF Function
In the above example, we use DATEDIF function to calculate the number of days, months and years between two dates using these formulas:
=DATEDIF(A5,B5,”d”) – for number of days
=DATEDIF(A6,B6,”M”) – for number of months
=DATEDIF(A7,B7,”Y”) – for number of years
Here, we have used cell references in the formula. However, you can also enter the date as text string like this:
=DATEDIF(“01/01/2015”, “31/10/2020”, “d”)
or you can enter the serial numbers representing the dates like this:
=DATEDIF(42005, 44135, “D”)
But you need to be careful when using the serial numbers as it is not fully reliable. This is because date numbering for different computer operating systems vary.
Note that the unit value can take an UPPER or lower case.
Example 2: Calculate Difference in Days
The Excel DATEDIF function calculates the number of days between two dates in three different ways using different units in the formula as shown below:
Note that the first formula can also be replaced with a simple calculation like this:
=”31/10/2021” – “01/03/2015”
And you will get the same result as: =DATEDIF(A5,B5,”d”). This is because Excel dates are basically serial numbers that you can perform calculations with.
In the above example, DATEIF function returns 2436 in the 1st row, which is the total number of all days from 01/03/2015 – 31/10/2021. In the 2nd row, it returns 30, which is the total number of days between 01 – 31 (ignoring the months and years). And the 3rd row returns 244, which is the total number of days between 01/03 – 31/10 (ignoring the years).
Example 3: Calculating Difference in Months
DATEDIF function calculates the difference in months in two ways. It calculates in the complete months including the years, as well as the complete months ignoring the years.
In the above example, we use both formulas to calculate the difference in the number of completed months between the 01/03/2015 and 31/10/2021.
The first formula counts all the completed months and returns 79 months. The second formula returns 7 (10 – 3 = 7) which counts only the months and ignores the years.
This can be used when you want to calculate the number of months between the start and end date of any project.
Example 4: Calculate Difference in Years
Excel DATEDIF function calculates the difference in number of years between two dates in complete years.
There is only one method to do this using the formula:
In the above example, Excel DATEDIF function calculates the number of complete (12 months) years between 01/03/201 and 31/10/2021 and returns 6 and 5 years respectively. It ignores the months and days after it.
If you look at the second row, the difference is almost 6 years, but because Excel DATEDIF formula calculates only the number of complete years (12 months), it returns a result of 5.
To get a more accurate result, use Excel YEARFRAC function as shown above.
This formula is commonly used to calculate the age in years.
Example 5: Use DATEDIF Function with other Function
Excel DATEDIF function can be used alone or with other excel functions.
For example, you can use DATEDIF function and TODAY function to calculate the current age from the date of birth using the formula:
For instance, if you want to know how many years and months in the date, then you can use YM in addition to get the months as shown below:
The first row is 63years 9months, the second row is 42years 4months, and the third row is 22years.
- #NUM! error – This error occurs when the start_date is greater than the end_date.
- #VALUE! error – This is the error you get when you enter an invalid date. This means that Excel is unable to recognize the date specified.
Other Related Excel Functions
Thank you for taking your time to learn Excel DATEDIF function. You can master Microsoft Excel by learning other Excel functions.
To learn more, check out these other functions in Excel:
- EDATE Function