Converting a date of birth to number of days (or months)

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).

It turn out that Excel can do this with one function:


  • FROM - is the Date you are measuring from (in our case the D.O.B)
  • TO - is the Date you are measureing 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
=TODAY() will return today's date (in this case 01/12/2016

= DATEDIF(A1,Today(),"y") - would count 44
= DATEDIF(A1, Today(),"ym") - would count 7
=DATEDIF(A1, Today(),"md") - would count 25

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: 44 years 7 months 25 days

=DATEDIF(AQ, TODAY(),"d") - shows us that there have been 16310 since that date.

comments powered by Disqus