The DATEDIF Function in Excel calculates the difference between two dates. This blog explains the entire usage of the DATEDIF formula in Excel.
Here we go 😎
- When To Use DATEDIF Function In Excel
- Syntax and Arguments
- Examples To Learn DATEDIF Function of Excel
When To Use DATEDIF Function In Excel
The expression DATEDIF represents “Dates Difference”. The excel DATEDIF function finds the difference between two dates in days, months, or years. The function is categorized as the date/time function of Excel.
One of the practical applications of the excel DATEDIF formula is to find the number of working days of an employee. Similarly, we can use this formula to find the age of a person in years.
Surprisingly, when you type =DATEDIF in Excel, there is no syntax. This is because DATEDIF is a hidden formula in excel.
Syntax and Arguments
- start_date – As the name says, mention the initial date.
- end_date – In thie argument, specify the ending date.
- unit – This is the format to get the difference of two dates. See the table below specifying possible ‘unit’ values.
|“D”||Returns the number of days between the two dates.|
|“M”||Gives the number of months between two dates.|
|“Y”||Returns the number of years between two dates.|
|“MD”||Gives the number of days between the two dates while ignoring the months and years.|
|“YD”||Gives the number of days between the two dates while ignoring the years.|
|“YM”||Gives the number of months between two dates while ignoring the years.|
Examples To Learn DATEDIF Function of Excel
Now that you have understood the syntax and arguments, let us try some examples to learn the usage of the DATEDIF Formula in Excel and how it works.
Ex. 1 – Finding Number of Days Using DATEDIF Formula in Excel
In this example, let us suppose we have the joining date of the students in an academy. Some of the students left the academy. Consequently, their charges would now be based on the number of days they studied there.
In order to find the number of days each of these students studies, enter this formula.
As a result, the function has returned 14.
Explanation – We have passed three arguments to the function. Start_date is A2, End_date is B2 while the unit is “D”. So the function returns the difference between two dates in number of days terms.
Copy the formula to rest of the records in column C. Or you may even use the fill handle tool to copy formula to cells below.
Ex. 2 – Finding Difference of Dates in Months and Years
We can easily find the difference between the two dates in months and years using “M” or “Y” as the unit argument. Let us suppose we have the starting date and the ending date, as shown below:
Enter the following DATEDIF formula to know the number of months between the two dates.
Similarly, below formula will find the number of years between two dates.
Explanation – In both formulas, the start_date argument is A2 and the end_date argument is B2. In the first formula the unit is “M”. As a result, the first formula returns the number of months between the dates 05-07-2015 and 11-10-2021. While in the second formula, the unit is “Y”. Consequently, the formula returns the number of years between 05-07-2015 and 11-10-2021.
Ex. 3 – Number of Weeks Between Two Dates – DATEDIF Formula in Excel
There is no specific unit argument defined for finding the number of weeks between two dates.
Surprisingly, we can only use a trick for that. 😉
Let us say we want to find the number of weeks a person has been jogging.
Use the following DATEDIF formula in Excel to get the results.
As a result, the function returns number of weeks as 6.
Explanation – The start_date is A2 and the end_date is a formula TODAY() i.e. today’s date. The DATEDIF function will find the number of days between 07-07-2021 and today’s Date (18-08-2021). The result of the difference in dates is then divided by 7 to give the number of weeks lying between the two dates.
The TODAY function in Excel returns the present date. The function updates the date in the spreadsheet or in whatever formula we use with it.
Ex.4 – Using Other Unit Arguments in DATEDIF Function of Excel
In this example, we will understand the usage of the “MD”, “YD”, “YM” unit arguments.
Let us say we have the following starting and ending dates.
Enter the following formulas to get the results when using “MD”, “YD, “YM” as unit arguments.
As a result, the function has returned 4, 26, 5.
Explanation – In the first formula, the unit argument is “MD”. This will make the function count the number of days without considering the month and year. Consequently, the difference between 11 and 7 is 4.
In the second formula, the unit argument is “YD”. This will make the function count the number of days considering the months, but without considering the year. Therefore, the number of days between 15-10-2016 and 10-11-2021 is 26.
In the third formula, the unit argument is “YM”. This will make the function to count the number of months without considering the year. Therefore the number of months between 10-5-2019 and 11-10-2021 is 5.
This brings us to the end of this blog on using DATEDIF function in excel.
Thank you for reading 🙂
- DATE Function in Excel – Get Date Using Excel Formula
- EDATE Function in Excel – Adding Months to a Specific Date
- DATEVALUE function in Excel – Get Date Serial Number
- EOMONTH Function in Excel – Getting Last Day of Month
- How to Count Duration in Hours Between Two Dates?
- NETWORKDAYS Function – Count Days in Excel