Excel YEARFRAC Function

We can use the Excel YEARFRAC function to extract the number of days between two dates as a fraction of a specified year.  We will work through the process of using this function in the simple steps below.

Figure 1: Result of the Excel YEARFRAC function

General Formula

=YEARFRAC(start_date, end_date, [basis])

Formula

=YEARFRAC(B4,C4)

Setting up the Data

• We will set up our data by inputting the Start Date in Column B
• Column C will contain the End date
• Column D is where will we input the formula to derive the result

Figure 2: Setting up the Data

Applying the YEARFRAC function

• We will click on Cell D4
• We will input the formula below in the cell
  =YEARFRAC(B4,C4)
• Next, we will press the enter key

Figure 3: Applying the Excel YEARFRAC function

• We click again on Cell D4 and use the fill handle tool (a plus sign found at the base of Cell D4) to drag down the formula into the other cells.

Figure 4: Result using the Excel YEARFRAC function

Explanation

We use the YEARFRAC function to calculate the fractional year between two dates. This function will return with the decimal value which is the fraction of a year between the two periods. We can also use this function to calculate birthdate, retirement dates, number of years between two particular dates, etc.

Notes:

• If the dates entered are returning with an error, check whether they are introduced as text and change your approach by entering them with the DATE() function.
• If your dates are not valid, then you will have the #VALUE! Error.
• If basis>0 or if basis <4 then YEARFRAC function will return error #NUM!

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.