說明
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.
語法
YEARFRAC(start_date,end_date,basis)
Inputs
This function has the following arguments:
| 名稱 | 必要 | Valid input |
|---|---|---|
start_date |
是 | First 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. |
end_date |
是 | 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 |
沒有 | The type of day count basis to use (see below) |
範例
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 | 計算 | 附註 |
|---|---|---|
| 0 (default) | 30/360 | US convention |
| 1 | actual/actual | |
| 2 | actual/360 | |
| 3 | actual/365 | |
| 4 | 30/360 | European convention |
Basis 0 (the default) and basis 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.
範例資料
| A (start date) |
B (end date) |
C (basis) |
|---|---|---|
| 2/28/2023 | 12/6/2024 | 1 |
| 36711 | November 30, 2000 | 2 |
| 7/19/2003 | Friday, May 31, 2002 | 3 |
| 12/23/2013 | January 23, 2014 | 4 |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=YEARFRAC("12/23/2013","March 23, 2015") |
Determines the fractional year difference between the two supplied dates, with no basis specified. | 1.25 |
=YEARFRAC(A1,B1) |
Determines the fractional year difference between the date in cell A1 and the cell B1, with no basis specified. | 1.76666666666667 |
=YEARFRAC(A1,B1,2) |
Determines the fractional year difference between the date in cell A1 and the cell B1, basis 2. | 1.79722222222222 |
=YEARFRAC(A4,B4,C4) |
Determines the difference between the date in cell A1 and the cell B1, using the European method, identified by referencing cell C1. | 0.0833333333333333 |
附註
- 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.
Related functions
DATE
DATEVALUE
DAY
DAYS
DAYS360
EDATE
EOMONTH
MONTH
NETWORKDAYS
NETWORKDAYS.INTL
WEEKDAY
WORKDAY
WORKDAY.INTL
YEAR
YEARFRAC