How to use YEARFRAC Function in Excel

The Excel YEARFRAC function returns a decimal value that represents fractional years between two dates. You can use YEARFRAC to do things like calculate age with a birthdate.

Syntax: =YEARFRAC (start_date, end_date, [basis])

The YEARFRAC function syntax has the following arguments:

  • Start_date (required argument) – This is the start of the period. The function includes the start date in calculations.
  • End_date (required argument) – This is the end of the period. The function also includes the end date in calculations.
  • [basis] (optional argument) – Specifies the type of day count basis to be used.
    Basis Day count basis
    0 or omitted US (NASD) 30/360
    1 Actual/actual
    2 Actual/360
    3 Actual/365

    European 30/360

Example: Let’s look at some Excel YEARFRAC function examples and explore how to use the YEARFRAC function as a worksheet function in Microsoft Excel:

YEARFRAC Function - How to use YEARFRAC Function in Excel

Syntax:  =YEARFRAC(A2,B2,1)


YEARFRAC Function in Excel - How to use YEARFRAC Function in Excel

Based on the Excel spreadsheet above, the following YEARFRAC examples would return:

Syntax: =YEARFRAC(A3,B3)
Result: 0.75

Syntax: =YEARFRAC(A4,B4)
Result: 1

Syntax: =YEARFRAC(A5,B5,1)
Result: 0.330601093

Syntax: =YEARFRAC(A6,B6,3)
Result: 0.583561644

Syntax: =YEARFRAC(A7,B7)
Result: 0.75

Syntax: =YEARFRAC(A8,B8)
Result: 0.25

Syntax: =YEARFRAC(A9,B9,3)
Result: 0.668493151

Syntax: =YEARFRAC(A10,B10,3)
Result: 0.832876712

Syntax: =YEARFRAC(A11,B11)
Result: 0.416666667

Syntax: =YEARFRAC(A12,B12)
Result: 0.416666667


  1. If the dates you are using are not working, input them with the DATE() function.
  2. #NUM! error – Occurs when the given basis argument is less than 0 or greater than 4.
  3. #VALUE! error – Occurs when:
    1. The start_date or end_date arguments are not valid dates.
    2. The given [basis] argument is non-numeric.

Add a Comment

Your email address will not be published. Required fields are marked *