How to Calculate the Percent of Year Completed and Remaining in Excel

Excel is a powerful tool for working with dates and time. One of the common tasks that you may encounter is to calculate the percent of year completed and remaining based on a given date. For example, you may want to know how much of the current year has passed or how much is left until the end of the year. In this article, we will show you how to do this using Excel formulas.

The Basic

The basic idea behind calculating the percent of year completed and remaining is to compare the given date with the start and end dates of the year. The start date of the year is always January 1st, and the end date of the year is always December 31st. The percent of year completed is the fraction of the year that has elapsed from the start date to the given date. The percent of year remaining is the fraction of the year that is left from the given date to the end date.

To calculate the fraction of the year, we can use the YEARFRAC function in Excel. This function returns the number of years between two dates as a decimal value. For example, YEARFRAC(1/1/2021,6/30/2021) returns 0.5, which means that half a year has passed between January 1st and June 30th in 2021. By default, the YEARFRAC function uses a 30/360 day convention, assuming that a year has 360 days and each month has 30 days. This is controlled by an optional argument called basis, which can be changed to use different day count methods.

Procedures

To calculate the percent of year completed and remaining in Excel, we can use the following generic formulas:

• Percent of year completed: =YEARFRAC(DATE(YEAR(date),1,1),date)
• Percent of year remaining: =1-YEARFRAC(DATE(YEAR(date),1,1),date)

In these formulas, date is the cell that contains the given date. The DATE function is used to create the start date of the year based on the year extracted from the given date. The YEAR function is used to get the year of the given date as a four-digit number. The YEARFRAC function is used to calculate the fraction of the year between the start date and the given date. The result is then formatted as a percentage using the Percentage number format in Excel.

Explanation

To illustrate how these formulas work, let’s use an example scenario. Suppose we have a table that shows the sales revenue of a company for each month in 2021. We want to calculate the percent of year completed and remaining for each month, as well as the total sales revenue for the year so far and the projected sales revenue for the rest of the year. The table looks like this:

To calculate the percent of year completed for each month, we can use the formula =YEARFRAC(DATE(YEAR(B2),1,1),B2) in cell C2 and copy it down to cell C13. This formula takes the date in column B and compares it with the start date of the year, which is January 1st of the same year. The YEARFRAC function returns the fraction of the year that has passed between these two dates. For example, for January, the formula is =YEARFRAC(DATE(YEAR(B2),1,1),B2) which is equivalent to =YEARFRAC(1/1/2021,1/31/2021). This returns 0.083333, which means that 8.33% of the year has completed in January. The result is then formatted as a percentage using the Percentage number format in Excel.

To calculate the percent of year remaining for each month, we can use the formula =1-YEARFRAC(DATE(YEAR(B2),1,1),B2) in cell D2 and copy it down to cell D13. This formula subtracts the fraction of the year completed from 1, which gives the fraction of the year remaining. For example, for January, the formula is =1-YEARFRAC(DATE(YEAR(B2),1,1),B2) which is equivalent to =1-0.083333. This returns 0.916667, which means that 91.67% of the year is remaining in January. The result is also formatted as a percentage using the Percentage number format in Excel.

To calculate the total sales revenue for the year so far, we can use the formula =SUM($C$2:C2) in cell E2 and copy it down to cell E13. This formula sums up the sales revenue from January to the current month. For example, for January, the formula is =SUM($C$2:C2) which is equivalent to =SUM($C$2:$C$2). This returns $10,000, which is the same as the sales revenue in January. For February, the formula is =SUM($C$2:C3) which is equivalent to =SUM($C$2:$C$3). This returns $22,000, which is the sum of the sales revenue in January and February.

To calculate the projected sales revenue for the rest of the year, we can use the formula =E2/D2 in cell F2 and copy it down to cell F13. This formula divides the total sales revenue for the year so far by the percent of year remaining, which gives an estimate of the total sales revenue for the whole year. Then, it subtracts the total sales revenue for the year so far from the estimate, which gives the projected sales revenue for the rest of the year. For example, for January, the formula is =E2/D2 which is equivalent to =$10,000/0.916667. This returns $10,909.09, which is the estimate of the total sales revenue for the whole year. Then, it subtracts $10,000 from $10,909.09, which gives $909.09, which is the projected sales revenue for the rest of the year.

The final table looks like this: