Use the YEARFRAC function to calculate the fraction of a year between two dates (inclusive of the start date, exclusive of the end date).
The function uses a basis (day-count convention) to count the number of days between these dates, and then divide that number by the basis.
Syntax
YEARFRAC(start, end[, basis])
The YEARFRAC function has the following arguments:
| Argument | Data type | Description |
| start (required) | Date | The start date. |
| end (required) | Date | The end date. |
| basis (optional) | Number |
The basis determines how many days exist in a year. A full year has:
US 30/360 is the default basis for DURATION. It can also be specified by entering 0. To use a different type of day count basis, enter:
Learn about the conventions used to calculate the day count for basis. |
| Returns |
| Number |
Constraints
The YEARFRAC function has the following constraints:
- the start and end values must represent a valid date;
- the start and maturity dates must represent a valid date between 01/01/1900 and 12/31/2399; and
- the basis, when specified, must be either 0 (US 30/360), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), or 4 (EUR 30/360).
Examples
The following tables show some example formulas using the YEARFRAC function.
You can reference line items or list properties in your formula.
| Formula | Description | Result |
| YEARFRAC(DATE(2015, 1, 15), DATE(2018, 4, 30), 1) |
This example shows a YEARFRAC calculation that includes a basis. The start date is given as 01/15/2015, the end date is given as 04/30/2018, and the basis is given as 1 (Actual/Actual). |
3.28767123 |
| YEARFRAC(DATE(2015, 1, 15), DATE(2018, 4, 30)) |
In this example YEARFRAC is calculated without specifying a basis. As a result, it uses the default basis of US 30/360. The start date is given as 01/15/2015 and the end date is given as 04/30/2018. |
3.29166666 |