Home » Excel-Built-In-Functions » Excel-Date-And-Time-Functions » Excel-Yearfrac-Function

Excel YEARFRAC Function

Related Function:
DAYS360 function

Function Description

The Excel Yearfrac function returns the fraction of a year that is represented by the number of whole days between two supplied dates.

The syntax of the function is:

YEARFRAC( start_date, end_date, [basis] )

Where the arguments are as follows:

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] -

An optional argument, which specifies the type of day count basis to be used.

Possible values of [basis] and their meanings are:

[basis] Day Count Basis
0 (or omitted) US (NASD) 30/360
1 actual/actual
2 actual/360
3 actual/365
4 European 30/360
The financial day count basis rules are explained in detail on the Wikipedia Day Count Convention page


Note that Microsoft advises that you do not type dates directly into functions, as Excel may interpret text representations of dates differently, depending on the date interpretation settings on your computer. Therefore the start_date and end_date arguments for the Yearfrac function should be input as either:

or

Yearfrac Function Examples

The spreadsheet below shows five examples of the Excel Yearfrac function. In each case, the function is used to calculate the year fraction between January 1st, 2015 and March 31st, 2015. However, each case uses a different Day Count Basis.

 Formulas:
  A B C
1 Start Date: 01/01/2015  
2 End Date: 03/31/2015  
3      
4 Yearfrac between Jan 1st & Mar 31st, 2015:
5 =YEARFRAC( B1, B2 ) - US (NASD) 30/360 basis
6 =YEARFRAC( B1, B2, 1 ) - actual/actual basis
7 =YEARFRAC( B1, B2, 2 ) - actual/360 basis
8 =YEARFRAC( B1, B2, 3 ) - actual/365 basis
9 =YEARFRAC( B1, B2, 4 ) - European 30/360 basis
 Results:
  A B C
1 Start Date: 01/01/2015  
2 End Date: 03/31/2015  
3      
4 Yearfrac between 1st Jan & 31st Mar 2015:
5 0.25 - US (NASD) 30/360 basis
6 0.243835616 - actual/actual basis
7 0.247222222 - actual/360 basis
8 0.243835616 - actual/365 basis
9 0.247222222 - European 30/360 basis

For further examples of the Excel Yearfrac function, see the Microsoft Office website.


Yearfrac Function Errors

If you get an error from the Excel Yearfrac function, this is likely to be one of the following:

Common Errors
#NUM! - Occurs if the value of the supplied [basis] argument is less than 0 or greater than 4.
#VALUE! -

Occurs if either:

  • The start_date or end_date arguments are not valid dates;
  • The supplied [basis] argument is non-numeric.
Return to the Excel Date and Time Functions Page
Return to the List of All Built-In Excel Functions