Happy Friday Excellers. Welcome back to another #FormulaFriday #Excel blog post in my 2020 Excel series. Today I want to share with you an old function in Excel that you can’t really find any help on recently. BUT, it’s a great one. It will help you to find the difference between two dates in a number of different variables such as years, months, days, hours etc. The function is DATEDIF.
What Does DATEDIF Mean?.
So, this function actually stands for Date Difference and is really useful for calculating employees age, length of service of an employee or the length of a loan for example.
DATEDIF Syntax.
The syntax of the function is a follows.
=DATEDIF(Date1, Date2, Interval)
Where
Date1 is the first date, Date2 is the second date and the Interval is the interval type to return.
There are a few different codes you can use for the Interval and a summary can be seen below,
Let’s work through an example and see how it works.
Calculate The Length Of Service For An Employee (in years).
First, type A1 type 01/12/1990 in cell A1. Next, type 15/11/2010 in cell B1. These are the first and second arguments in our function. Next, in cell A4 type your formula DATEDIF(A1,A2,”Y”). This should give you an answer of 19.
Go ahead and try the rest of the intervals. The expected answers are below.
D=7289
Y=19
YM=11
YD=349
MD=14
Add More Detail To The Difference Between Two Dates.
Now, this all may look at little too literal, and it usually is the case that for payroll or length of service you would want to know then years, months and days a person has worked, that, of course, can be done in Excel just by turning up the volume on the formula.
So, if we use the same example that we want to find out the years, months and days that someone had worked for a company for payroll reasons then your formula would look like this.
DATEDIF(A1,A2,”y”)&” years “&DATEDIF(A1,A2,”ym”)&” months “&DATEDIF(A1,A2,”md”)&” Days”
The result in this instance would be 19 years, 11 months and 14 days.
However, if your formula result contains a zero (for example, 0 Years, 11 Months and 14 Days), you might want to suppress your zeros, so you only get the result 11 Months and 14 Days.
We can achieve this with a little tweaking of the formula by adding in a sneaky IF statement. The result of the formula is below.
=IF(DATEDIF(A1,A2,”y”)=0,””,DATEDIF(A1,A2,”y”)&” years “)&IF(DATEDIF(A1,A2,”ym”)=0,””,
DATEDIF(A1,A2,”ym”)&” months “)&DATEDIF(A1,A2,”md”)&” days”
Notes On The DATEDIF Function.
If the interval is typed into the formula then as it is a string, it needs to be set in “” double quotes, if it is referred to in a cell reference there is no need and can be left without the “” double-quotes.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Formula Friday Series Click The Link Below
