YEARFRAC(start_date, end_date [,basis])

Returns the number of years as a decimal between two dates.

start_dateThe starting date.
end_dateThe finishing date.
basis(Optional) The type of day counting to use:
0 = US 30/360 (default)
1 = Actual/Actual
2 = Actual/360
3 = Actual/365
4 = European 30/360

Remarks

* All arguments are truncated to integers.
* Dates may be entered as text strings within quotation marks, as serial numbers or as results of other formulas or functions.
* If "start_date" or "end_date" are not valid dates, then #NUM! is returned.
* If "start_date" can be a text string or a date serial number.
* If "basis" is left blank, then 0 is used.
* If "basis" is not an integer, it is truncated.
* If "basis" < 0, then #NUM! is returned.
* If "basis" > 4, then #NUM! is returned.
* If "basis" = 0 (US 30/360), the calculation is based on 30 days in every month, divided by 360.
* If "basis" = 1 (Actual/Actual), the calculation is based on the actual number of days in the month, divided by the actual number of days in the year.
* If "basis" = 2 (Actual/360), the calculation is based on the actual number of days in the month, divided by 360.
* If "basis" = 3 (Actual/365), the calculation is based on the actual number of days in the month, divided by 365.
* If "basis" = 4 (European 30/360), the calculation is based on 30 days in a month, divided by 360.
* You can use the DAYS360 function to return the number of days between two dates, based on 30 day months.
* You can use the YEAR function to return the year as an integer given a date serial number.
* In Excel 2003 this function was only available when you had the Analysis ToolPak add-in loaded.
* For the Microsoft documentation refer to support.office.com

 A
1=YEARFRAC("2001/01/25","2001/09/27") = 0.672
2=YEARFRAC("01/01/2018","31/12/2018") = 1
3=YEARFRAC("01/01/2018","31/07/2018") = 0.583333333333333
4=YEARFRAC("01 Jan 2020","01 April 2020",0) = 0.25
5=YEARFRAC("01 Jan 2020","01 April 2020",1) = 0.248633879781421
6=YEARFRAC("01 Jan 2020","01 April 2020",2) = 0.252777777777778
7=YEARFRAC("01 Jan 2020","01 April 2020",3) = 0.249315068493151
8=YEARFRAC("01 Jan 2020","01 April 2020",4) = 0.25
9=YEARFRAC("01/01/2018","31/04/2018") = #VALUE!
10=YEARFRAC("1/1/1998","6/30/1998",2) = #VALUE!
11=YEARFRAC("1/1/1998","6/30/1998",0) = #VALUE!


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top