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.
YEARFRAC(start, end[, basis])
The YEARFRAC function has the following arguments:
|start (required)||Date||The start date.|
|end (required)||Date||The end date.|
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.
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).
The following tables show some example formulas using the YEARFRAC function.
You can reference line items or list properties in your formula.
|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).
|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.