Excel DATEDIF formula – In this excel tutorial, we will learn how to use the Datedif function in Microsoft Excel to calculate the difference between two dates.
Understanding the Excel DATEDIF Formula
The DATEDIF function is a function in Microsoft Excel that is used to calculate the difference in dates in units of days, months or years.
The Excel DATEDIF function allows you to calculate the number of years, months, or days between two specified dates. This function is commonly used to calculate age or calculate years of service in excel .
When typing =DATEDIF on the formula bar, you will not find the DATEDIF function in the function list or excel formula.
Even though the Intellisense feature or sometimes also called Auto complete doesn’t show the DATEDIF function, it doesn’t mean that your excel has a problem or the datedif function doesn’t exist and you can’t use it.
This DATEDIF function is basically an Excel function provided for compatibility with Lotus 1-2-3, and can still be used on all versions of excel including microsoft excel 2007, 2010, 2013, 2016 and newer office excel versions.
So, to activate the DATEDIF function that is not on the insert function menu, you must type this DATEDIF formula manually by typing the function name directly in the formula bar or active cell where you will use this excel formula.
If the DATEDIF formula you’re using returns a #NAME error , you probably wrote the function name incorrectly. The correct one is “DATEDIF” and not “DATEIF”.
How to Use Excel’s DATEDIF Function
How to use the DATEDIF function as an excel formula is as follows:
Code
DATEDIF(StartDate,EndDate,Unit Code)Information:
- start date
To be completed with the start date of the date period or earlier date
- end date
Filled with the end date of the period or the last date to be calculated
- UnitCode
Filled with the code information you want to get. Please see the next section for the codes that you can use in this excel DATEDIF function
Unit Code in Excel DATEDIF Function
The UnitCode argument in the excel DATEDIF function or formula that you can use to get date difference information in excel is as follows:
Excel DATEDIF Formula Example
The following is an example of a datedif formula in Microsoft Excel.
In the first example the DATEDIF excel formula used is:
=DATEDIF(B3;C3;”Y”)
The “Y” code in the datedif formula is used to calculate the number of even years that have passed between January 01, 2019 and March 13, 2023. The result is 4.
The second example formula is:
=DATEDIF(B4;C4;”M”)
The datedif formula in this second example uses the unit code “M” which is used to calculate the difference in dates in even-numbered months between January 01, 2019, and March 13, 2023. The result is 50.
Example of the third DATEDIF formula:
=DATEDIF(B5;C5;”D”)
The datedif formula in this third example uses the unit code “D”, which is the unit code in the DATEDIF function that is used to calculate the number of days that have passed between January 01, 2019 and March 13, 2023. The result is 1532.
In the fourth DATEDIF excel formula example:
=DATEDIF(B6;C6;”MD”)
The code “MD”, is used to get the difference in the date in days between January 1, 2019 and March 13, 2023 after deducting the year and month that have even passed. So by using this code, the difference between year and month is ignored. The end result is 12.
In the fifth example:
=DATEDIF(B7;C7;”YM”)
In this example, the datedif formula uses the unit code “YM” which is used to find the difference in months between the start and end dates, ignoring the even number of years that have passed and the number of days remaining between the two dates. The result is 2.
In the sixth example:
=DATEDIF(B8;C8;”YD”)
The code used by excel’s datedif function in the formula is “YD”, which is the code used to calculate the difference in dates in days, ignoring the even number of years that have passed. The result is 71.
Notes on Using Excel’s Datedif Function
There are several things you need to pay attention to when using the DATEDIF function in Microsoft Excel.
First , if the DATEDIF formula you are using generates an Error#NAME?, it’s possible that you wrote the function incorrectly. Because this function does not appear as autocomplete when writing formulas, excel users usually write this function incorrectly as DATEIF, which should be written as DATEDIF as stated in the previous section.
Second , if the value of the InitialDate Argument is greater than the EndDate, the Datedif formula will produce an error value.#NUM!. I also often encounter this error where the date argument entered is reversed.
Third , you should not use the unit code “MD”, because as stated on the official Microsoft website , this code may produce negative numeric values, zero values, or inaccurate results.
That’s all our discussion about the DATEDIF function or formula in Excel this time, if there are problems, please convey them in the comments column provided below. Thank you.
