The DATEDIF() function is almost a secret as it is essentially undocumented by Microsoft although you will find a help page on the MS web site. However, the function is very helpful for anyone who needs to find the age of something or to find how long something has been going on.
The syntax of DATEDIF() is
- Date1 is the from date … the oldest date
- Date2 is the to date … the latest date
- Interval tells Excel whether you want the answer in
- Y = years
- M = months
- D = days
- YM = remainder months having found years and days
- YD = remainder days having found the years
- There is another interval option MD but even MS says don’t use it!
Let’s find the age of my dog that was born on 16th February 2015
If I enter just =DATEDIF(16/2/2015,22/2/2018,”Y”) I will get 3 years … but that’s not the final answer, which I want to be in the format: Y … M … D.
To find the complete answer I need to create three formulas, with the birth date in B14
- =DATEDIF(B14,TODAY(),”Y”) = 3 years
- =DATEDIF(B14,TODAY(),”M”) = 0 months
- =DATEDIF(B14,TODAY(),”D”) = 7 days
So my dog is 3 years no months and 7 days old, on 22nd February 2018
Download my Excel file to see how I created a much simpler version of these answers:
You are now a DATEDIF() expert but try your hand at these questions, too, before you download and explore my file:
Question 2: How many days have I lived if I was born on 12th November 1952?
Question 3: Your Asset Register shows the following three assets as having been acquired at the dates given: find their ages in months
|Car||22nd February 2014|
|Cardboard forming machine||1st January 2001|
|Canteen furniture||19th September 1999|
The answers are fully worked in my file!
That’s it! A genuine Excel secret unveiled! It could save you hours of frustration if nothing else!
22nd February 2018
Excel file here datedif_function