Calculate age in years, months and/or days

Calculating the age of someone from their date of birth or, equivalently, the duration of time between two specified dates (ie DOB and now) is a question that comes up on the Forum regularly; a quick search on "+calculate +age" lists over 50. This forum post has quite a good discussion: Calculate Age in ODK

The prevailing solution is, one way or another, to take the difference in the number of days between two dates, via decimal-date-time() [which is measured relative to the standardized Unix epoch date of Jan 1, 1970; see https://en.wikipedia.org/wiki/Unix_time]. This will give a precise measurement of the number of the days between, say, today and the target date-of-birth. From the days delta value you can divide by 365 days-in-a-year (or 365.25, or 365.2425...) to approximate the fractional number of years, or 'age'.

However, it is important to appreciate that this result is only approximate, because the actual number of days will vary depending on how may intervening leap years occur. If you happen to have two calendar dates which are roughly the same then you could potentially end up being off by 1 year (when truncated) as a result of the varying days-in-year discrepancy [aside: you can potentially get around this by using round() for the years, instead of truncating with int(), but this isn't really what we mean when talking about 'age'; eg my birthday is Nov 20, but that doesn't mean they're going to let me into a bar just because it's Nov 19... :slight_smile: ]

If you need a precise age in years then the following steps will provide the correct value.

1. Date of birth

The first step is obviously to acquire the target date-of-birth which a simple date question will capture. The algorithm we will use to calculate age requires this date to be broken out into its component day, month and year values. Internally, dates are stored as a normal strings in the format "YYYY-MM-DD". Although we could use regular string functions to extract these components, eg substr(), it is a bit more elegant to use the specific XPath function intended for such purposes: format-date()

year = format-date(${dob}, '%Y')
month = format-date(${dob}, '%n')
day = format-date(${dob}, '%e')

2. Is it a leap year

The age algorithm also requires determining the actual number of days in the birth month. If the birth month happens to be February then the number of days will depend on whether it is a leap year or not. So the first calculation is to determine if the DOB is a leap year. For this we use the formula from https://learn.microsoft.com/en-us/office/troubleshoot/excel/determine-a-leap-year

is_leap_year = if( (${year} mod 400)=0 or ((${year} mod 4)=0 and (${year} mod 100)!=0), 1, 0)

where 1 indicates it is a leap year, and 0 means it is not.

3. How many days in the birth month

Next, we calculate how many days are in the birth month. If it is a leap year and the birth month is February then it is 29 days, otherwise we can lookup the regular number of days for that month. For this I use a trick where I put all the numbers in a space-separate list and use the selected-at() function to look up the appropriate one [the 0 at the beginning of this list is because selected-at() is zero-indexed, whereas our months start with 1=Jan].

days_in_month = if((${is_leap_year}=1) and (${month}=2), 29, selected-at("0 31 28 31 30 31 30 31 31 30 31 30 31", ${month}))

4. When is today

As with Step 1, the age algorithm also needs the actual day, month and year components of the date we are referencing the date of birth against. This will typically be today(), although you can equally substitute any end date you wish to get a duration in years, months, days.

thisYear = format-date(${today}, '%Y')
thisMonth = format-date(${today}, '%n')
thisDay = format-date(${today}, '%e')

5. The interesting bit...

Now that we have all the required raw data, we can run the algorithm to calculate the age in years, months, and days. The algorithm is described in detail here: https://www.geeksforgeeks.org/program-calculate-age/, along with associated code which I have transcribed into equivalent XForms/XPath calculations. The general approach is that you first compare the start and end day number, and if the end day is less than the start day then you add the number of days in that month to the end day, and subtract 1 from the end month to compensate for the change. You then do much the same with months: if the end month is less than the start month then you add 12 months to the end month, and subtract 1 from the end year to compensate. Finally, you just take the difference between the start and (adjusted) end day, month and year values.

endDay = ${thisDay} + if(${day}>${thisDay}, ${days_in_month}, 0)
modifiedMonth = ${thisMonth} - if(${day}>${thisDay}, 1, 0)
endYear = ${thisYear} - if(${month}>${modifiedMonth}, 1, 0)
endMonth = ${modifiedMonth} + if(${month}>${modifiedMonth}, 12, 0)

years = ${endYear} - ${year}
months = ${endMonth} - ${month}
days = ${endDay} - ${day}

Results

At the end you have the difference between the start and end dates expressed in terms of the actual number of differing days, months and years, which is arguably what we desire when looking at a calendar. So if you want your 'age' in whole years then you have it. If you happen to want the 'age' in months then it is just a matter of multiplying the years by 12 and adding:

age_months = ${years}*12 + ${months}

If you really do just want the age (or duration) in days then all of the above is quite unnecessary, and you will get the most accurate value via the original approach, of taking the difference between each date relative to the Unix epoch:

age_days = int(decimal-date-time(${today})) - int(decimal-date-time(${dob}))

Below is an XLSForm with the algorithm. Have a play, adapt to your needs, and let me know what you think.

  • Gareth

calculate_age.xlsx (9.4 KB)





5 Likes