excel

Difference between dates

By
wall planner dates
School's often process data on a learner's date of birth when looking at cohort data.
 
I was asked recently to help convert dates of birth to a statement of how many years, months and days had passed since the learner was born.  
 
On face value, this is quite complex:
  • Count forward from the day to the end of the month (and store the number of days)
  • Count forward from the month to the end of the year (and store the number of months)
  • Count forward from the year to the current year (and store the number of years) - remembering to take one off for the previous adjustment
  • Remembering to take into account leap years?
  • Arrrrgh - easy to get into a knot.
The same school wanted to know into total, how many months old a learner was, and whilst we are at it, how many days (tricky because of leap years).
 
Excel date formula
 
It turns out that Excel can do this with one function:
 
=DATEDIF(FROM, TO, Measure)
 
Where:
  • FROM - is the Date you are measuring from (in our case the D.O.B)
  • TO - is the Date you are measuring to (in our case, today's date)
  • Measure, is how you want to count:
    • "y" counts full years since the date
    • "ym" counts the remaining months of the year
    • "md" counts the remaining days of the month
    • "m" would count the total months since the date
    • "d" would count the total number of days
 
So for example:
 
In A1, we have 06/04/1972
In B1, = TODAY() will return today's date (in this case 29/11/2022)
 
= DATEDIF(A1,Today(),"y") - would count 50
= DATEDIF(A1, Today(),"ym") - would count 7
= DATEDIF(A1, Today(),"md") - would count 23
 
We can Concatenate them together to give:
 
=DATEDIF(A1,TODAY(),"y")&" years "&DATEDIF(A1,TODAY(),"ym")&" months "&DATEDIF(A1,TODAY(),"md")&" days" -- which would display:  50 years 7 months 23 days
 
=DATEDIF(A1, TODAY(),"d") - shows us that there have been 18499 since that date.