Related Function:

The YEARFRAC function returns the fraction of a year between two dates as a decimal number.

Syntax

=YEARFRAC(start_date,end_date,[basis])

Arguments

Argument Description
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

  [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: 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.

Examples

  A B C
1 Data    
2 Tue 20-Dec-2011 Start date  
3 Sun 15-Jul-2014 End date  
4      
5 Formula Result Notes
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