YEARFRAC function calculates the number of fractional years between two dates.
|start_date||Date||Yes||The date to measure from|
|end_date||Date||Yes||The date to measure to|
The length of time from
end_date, given as a fraction of a year.
The US 30/360 method is used to calculate the fraction.
To find the exact number of days between two dates, use the DAYS function.
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.