When collecting the birthdate of a rights-holder, you may want to calculate their age or age group.
Calculating ages
The YEARFRAC function is useful in calculating ages based on birth date.
For example, if you have a date field for a beneficiary's date of birth, with the code DOB, then you can calculate their current age as:
YEARFRAC(DOB, TODAY())
Note that YEARFRAC does not always evaluate to a round number. For example, if the participant's date of birth is 1980-07-01 and today's date is 2023-01-01, then the result of YEARFRAC will be 42.5.
Normally we speak of ages as round numbers, rounding down. We can find someone's age by combining the YEARFRAC function with the FLOOR function, which rounds fractions towards zero:
FLOOR(YEARFRAC(DOB, TODAY()))
Age on a specific date
The TODAY function always evaluates to the current date, which means that the participant's age will change over time, and that your indicator results based on these ages would change over time. For this reason, you might prefer to use a fixed date for calculating participant's ages, such as the start of program:
FLOOR(YEARFRAC(DOB, DATE(2023,1,1))
The formula above will calculate the participant's age as of January 1st, 2023.
Finding an age group
You can further recode the calculated AGE field into a new AGE_GROUP field using the IF function:
IF(AGE < 18, "Minor", "Adult")
Multiple IF function calls can be nested to split a quantity into multiple categories, for example:
IF(AGE < 18, "< 18",
IF(AGE < 25, "18 - 24",
IF(AGE < 35, "25 - 34",
IF(AGE < 45, "35 - 44",
"45 and up"))))
The formula above would be calculated for each record and might yield the following table:
| AGE | GROUP |
|---|---|
| 8 | < 18 |
| 32 | 25 - 34 |
| 6 | < 18 |
| 60 | 45 and up |
| 18 | 18 - 24 |
