The YEARFRAC function returns the fraction of a year between two dates as a decimal number.
|start_date||The start of the period (this date is included in the calculation|
|end_date||The end of the period (this date is included in the calculation)|
|[basis]||Optional. Defines the day count basis to be used in the calculation
The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page
Note: The start_date and end_date should be input as either:
- References to cells containing dates, or
- Dates returned from formulas
If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.
Warning: Although you can input the date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.
|2||Tue 20-Dec-2011||Start date|
|3||Sun 15-Jul-2014||End date|
|6||=YEARFRAC(A2,A3)||0.569444||Fraction of the year between 12/20/2011 and 7/15/12, omitting the [basis] argument|
|7||=YEARFRAC(A2,A3,1)||0.568306||Fraction between same dates, using the Actual/Actual basis argument. Because 2012 is a Leap year, it has a 366 day basis|
|8||=YEARFRAC(A2,A3,3)||0.569863||Fraction between same dates, using the Actual/365 basis argument. Uses a 365 day basis|
Note: Once you have the decimal value, you can round the number if you like. For example, you could round to the nearest whole number: =ROUND(YEARFRAC(A3,B3),0).
Common Function Error(s)
|Problem||What went wrong|
|#VALUE!||Occurs if the start_date or end_date arguments are not valid dates|
|#NUM!||Occurs if the value of the [basis] argument is less than 0 or greater than 4|