The YEARFRAC function returns the fraction of the year represented by the date range limited by the dates passed as arguments.
YEARFRAC(
start_date,
end_date
[, basis]
)
- start_date: Start date of the range to be considered in datetime format or in text format, or reference to a column containing the start date.
- end_date: End date of the range to be considered in datetime format or in text format, or reference to a column containing the end date.
- basis: Optional argument. Number of days to consider per month and per year (it can be a reference to a column containing the base to be applied):
- 0: US (NASD) 30/360
- 1: Actual/Actual
- 2: Actual/360
- 3: Actual/365
- 4: European 30/360
The YEARFRAC function returns a real number.
The function considers the number of whole days between the two given dates, so that between January 1, 2016 at 00:00 and January 2, 2016 at 00:00, it will consider a single day.
If the optional basis argument does not take a value in the range 0-4, the function returns an error. This argument indicates how many days it will be considered to have each month and each year. Thus, between January 1, 2016 and January 31, 2016, 30 days will be considered for bases 0 and 4 (which assume that each month has 30 days) and 31 days for the rest of bases (which consider the actual number of days). Similarly, the year 2016 will be considered to have 360 days for bases 0, 2 and 4, 365 days for base 3 and the actual number of days for base 1 (366 in the case of 2016, as it is leap).
In this example, the range is limited by January 1 and January 31, 2016 (30 whole days):
- The base 0 assumes 30-day months and 360-day years, so the indicated range represents 30/360 = 8.33%.
- Base 1 assumes the actual number of days, both in the month and in the year: 30/366 = 8.1967% (8.20% rounded to two decimal places).
- Base 3 assumes the actual duration of months (30 days) and years of 365 days: 30/365 = 8.2191% (8.22% rounded to two decimal places).
In this other example, the range is limited by the dates January 1, 2016 and March 15, 2016:
- For the base 0, 30-day months and 360-day years are considered, so the calculation to be made is: (30 + 30 + 14) / 360 = 20.5555%. In this case the calculation implies 30 days of both January and February and 14 days of March.
- For base 1, months and years are considered with their actual number of days: (31 + 29 + 14) / 366 = 20.2185% (20.22% rounded to two decimal places).
- For base 3, months with their actual number of days and years of 365 days are considered: (31 + 29 + 14) / 365 = 20.2739%.
