Excel YEARFRAC Function

Want to learn more about how to find a fractional year between two dates in Excel? This post will give you an overview of how to use the YEARFRAC formula to find the number of days between two dates as a fraction of a year in Excel.

Syntax(Generic Formula)

  Generic Formula of YEARFRAC function

=YEARFRAC(start_date, end_date, [basis])

  1. Start_date (required) – the start of the period.
  2. End_date (required) –  the end of the period.
  3. [basis] (optional) – It specifies the type of day count basis to be used.

How the YEARFRAC function Works

In Excel, we use the YEARFRAC function to calculate the time between two dates(the start_date and the end_date) in decimal year format. This function returns a decimal result to indicate fractions of a year.

Figure 1: Example of finding the number of days between 2 dates as a year fraction

In the example selected cell shown, the formula in D5 is:

=YEARFRAC(B5,C5)

We can use this formula to calculate retirement dates, time since a birthday, number of years between two specific dates, etc.

Important

  • If the dates you are using are not working, check whether they are entered as text and try inputting them with the DATE() function.
  • If a start_date argument or end_date argument is not valid dates, then this function returns an error #VALUE!.
  • If basis>0 or if basis <4 then YEARFRAC  function will return error #NUM!
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar