Description
Use this function to return a decimal value that represents fractional years between two dates.
This function uses whole days between two dates to calculate the fraction of a year as a decimal number. For example, you can use it calculate age from a birthdate.
Syntax
YEARFRAC(start_date,end_date,basis)
Inputs
This function has the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
start_date |
Yes | First date of the range to be considered. | A cell reference, an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD. A formula which results in one of these is also acceptable. Quotation marks are required. |
end_date |
Yes | Last date of the range to be considered. | This can be a cell reference, an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD. A formula which results in one of these is also acceptable. Quotation marks are required. |
basis |
No | The type of day count basis to use. | See Basis Options description below. |
Example
Basis options
This function uses the optional argument basis to control how days are counted when making the calculation. The default behavior is to count days between the two supplied dates based on a 360-day year, where all 12 months are considered to have 30 days.
Here are the available options:
| Basis | Calculation | Notes |
|---|---|---|
| 0 (default) | 30/360 | US convention |
| 1 | actual/actual | |
| 2 | actual/360 | |
| 3 | actual/365 | |
| 4 | 30/360 | European convention |
A basis value of 0 (the default) and a basis value of 4 both operate based on a 360-day year, but they handle the last day of the month differently.
- With the US convention, when the start date is the last day of the month, it is set to the 30th day of the same month. When the end date is the last day of the month, and the start date less than 30 days earlier, the end date is set to the 1st of the subsequent month, otherwise the end date is set to the 30th of the same month.
- With the European convention, start dates and end dates equal to the 31st of a month are set to the 30th of the same month.
Sample data
| A | B | C | D | E | |
|---|---|---|---|---|---|
1 |
Start date | End date | Basis | Value | Rate (%) |
2 |
2/28/2023 | 12/6/2024 | 1 | $10,000.00 | .05 |
3 |
36711 | November 30, 2000 | 2 | $4500.45 | .10 |
4 |
7/19/2003 | Friday, May 31, 2002 | 3 | $5200.00 | .0425 |
5 |
12/23/2013 | January 23, 2014 | 4 | $1700.98 | .023 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Determine the fractional year difference between two supplied dates, with no basis specified. | =YEARFRAC("12/23/2013","March 23, 2015") |
This formula determines the fractional year difference between Result: 1.25. |
| Determine the fractional year difference between the date in two cells, with no basis specified. | =YEARFRAC(A2,B2) |
This formula determines the fractional year difference between the date in Result: 1.76666666666667. |
| Determine the fractional year difference between the the date in two cells, with a basis of 2. | =YEARFRAC(A3,B3,2) |
This formula determines the fractional year difference between the date in Result: 1.79722222222222. |
| Determine the fractional year difference between the the date in two cells, with a basis specified in a third cell. | =YEARFRAC(A4,B4,C4) |
This formula determines the fractional year difference between the date in Result: 0.0833333333333333. |
| Determine the proportional interest accrued on a loan that wasn't held for a full year. | =D2*E2*YEARFRAC(A2,B2,C2) |
This formula uses the following values:
Result: 885.0889193. |
Notes
- All arguments are truncated to integers.
- If
start_dateorend_dateare not valid dates, YEARFRAC returns the #VALUE! error. - If
basisis less than 0 or greater than 4, YEARFRAC returns the #NUM! error. - Wildcards don't work with this function.