How to calculate percent of year complete in Excel

by

If you need to calculate the percent remaining in a year, based on a given date, you do so with a formula based on the YEARFRAC function.

Formula

=YEARFRAC(DATE(YEAR(date),1,1),date)

Explanation

In the example, the formula in C5, which calculates percentage of year remaining, is:

=YEARFRAC(DATE(YEAR(B5),1,1),B5)

How the formula works

the YEARFRAC function takes two dates, a start date and and an end date, and calculates the time between them in fractional years. In this case, we are using the end date as-is, but generating a start date that equals the first day of the same year with this bit of code:

DATE(YEAR(B5),1,1)

This extracts the year value from the date in B5 and plugs it into the DATE function, along with a 1 for both month and day.

Worked Example:   Get age from birthday in Excel

The result is that the YEARFRAC function returns a decimal value that corresponds to the fraction of the year between the two dates.

Worked Example:   Calculate years between dates in Excel

When this value is formatted with the percentage number format, it shows percent of year complete.

Worked Example:   YEARFRAC function: Description, Usage, Syntax, Examples and Explanation

Percent of year remaining

To calculate percent of year remaining, just adjust the formula to subtract the fractional year from 1. In the example, D5 contains this formula:

=1-YEARFRAC(DATE(YEAR(B5),1,1),B5)

Leave a Reply

Your email address will not be published. Required fields are marked *