How to use Excel YEARFRAC function

Introduction

YEARFRAC function in Excel is a type of DATE/TIME function. It used to find year between two dates which can also be in form of a fraction and result can be in form of decimals. It is a worksheet function and can be entered in a cell.

Syntax

=YEARFRAC(Start_Date, End_Date, Basis)

Arguments

  • Start_Date:- A starting date in a valid excel format. Can be entered using DATE function, if dates in form of text are entered then there is a possibility of getting an error.  
  • End_Date:- An end date in a valid excel format. Can be entered using DATE function, if dates in form of text are entered then there is a possibility of getting an error.  
  • Basis: – It is type of day count system which will be used. Default value is zero having US convention.

Keynotes

  • In General formatting of Excel, Dates are in form of serial numbers. Serial number 1 is for 1 JAN, 1900 and the number increases from thereon. For getting it in readable form use the formatting function.
  • A valid Excel date must be used.
  • For text values #VALUE error is displayed.
  • If basis is less than zero or more than four, #NUM error is displayed.

BASIS

Basis Day count basis
0 or omitted US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4 European 30/360

Examples

In this example, start dates and end dates are entered in column A and B, and YEARFRAC Function is used to enter fractional values of year between the two dates as follows:

Leave a Reply

Close Menu