When collecting the birthdate of a rights-holder, you may want to calculate their age or age group.
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 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:
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:
The formula above will calculate the participant's age as of January 1st, 2023.
You can further recode the calculated
AGE field into a new
AGE_GROUP field using the IF function:
IF(AGE < 18, "Minor", "Adult")
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:
|32||25 - 34|
|60||45 and up|
|18||18 - 24|