Description
The YEARFRAC function calculates the number of fractional years between two dates.
Usage
YEARFRAC(startDate, endDate)
Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| start_date | Date | Yes | The date to measure from |
| end_date | Date | Yes | The date to measure to |
Result
The length of time from start_date to 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.
Examples
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()))
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.