How to Use YEARFRAC in Excel to Get a Fractional Year

When creating pivot reports or plans in Excel, it's often necessary to calculate the percentage of the year that has passed or remains. The percentage of days that have passed in the year can be used for other calculations or simply to inform report users.



How to Calculate the Fraction of a Partial Year as a Percentage?

For a clear example, the image below illustrates the concept:

YEARFRAC.

As shown in the Excel formula bar, the YEARFRAC function is used here. This function requires two mandatory arguments:

  1. Start_date – The starting date of the year's fraction.
  2. End_date – The ending date.

Based on these two values, the function calculates the fraction of the year corresponding to the number of days between the start and end dates.

To calculate the remaining percentage of the year, a simple arithmetic operation in the formula is enough. Subtract the result of the function from one:

=1-YEARFRAC(A2,B2)

How to Convert the Percentage of Days Passed in the Year into Months and Days

Now, let's convert the fraction into months and days by determining its duration and displaying it in time units. We'll use a more complex formula for this:

=DATEDIF(1,B1*365+1,"YM")&" months and "&DATEDIF(1,B1*365+1,"MD")&" days"
percentage of the year into months.

Or only in days:

=DATEDIF(1,B1*365+1,"D")&" days"
fraction of the year in days.

Download an Example of How to Use the YEARFRAC Formula in Excel download file

Now we know exactly how much of the partial year is in months and days, even if we don't know the start and end dates in advance.