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.