totn Excel Functions

MS Excel: How to use the YEARFRAC Function (WS)

This Excel tutorial explains how to use the Excel YEARFRAC function with syntax and examples.

Description

The Microsoft Excel YEARFRAC function returns the number of days between 2 dates as a year fraction.

The YEARFRAC function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the YEARFRAC function can be entered as part of a formula in a cell of a worksheet.

Excel YEARFRAC function

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the YEARFRAC function in Microsoft Excel is:

YEARFRAC( start_date, end_date, [basis] )

Parameters or Arguments

start_date
The start date to use in the calculation and should be entered as a serial date, not a text date.
end_date
The end date to use in the calculation and should be entered as a serial date, not a text date.
basis

Optional. It is the basis used to calculate the year fraction. It can be one of the following values:

Value Explanation
0 (default) US 30 (NASD)/360
1 Actual days/Actual of days in year
2 Actual days/360
3 Actual days/365
4 European 30/360

Note

  • Only the date portion (not time) of start_date and end_date is used in the calculation.
  • Only the integer portion of basis is used in the calculation. If a decimal value is provided, it will be truncated.
  • If either start_date or end_date is not a valid date, the YEARFRAC function will return the #VALUE! error.
  • If the integer portion of basis is not between 0 and 4, the YEARFRAC function will return the #NUM! error.
  • A serial date is how Excel stores dates internally and it represents the number of days since January 1, 1900.

Returns

The YEARFRAC function returns a numeric value.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

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

Excel YEARFRAC Function

In the spreadsheet above, the dates in column A and B have been entered as serial dates. To see the numeric value for any serial date, change the format of the cell to General.

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

=YEARFRAC(A2,B2)
Result: 0.83333333

=YEARFRAC(A3,B3)
Result: 0.25

=YEARFRAC(A4,B4)
Result: 0.5

=YEARFRAC(A5,B5)
Result: 1

You can also enter the date values using the DATE function as follows:

=YEARFRAC(DATE(2016,1,1),DATE(2016,12,31))
Result: 1

This formula would return the year fraction between Jan 1, 2016 and Dec 31, 2016.

Share on: