Go Back

Get percent of year complete

Read time: 25 minutes

In this tutorial, you will learn how to get percent of year complete for a date. This is possible by using a combination of Excel functions YEAR, DATE and YEARFRAC. YEAR function returns a year for a date. DATE function returns a date for a day, month and year. YEARFRAC function returns a percentage of the year completed for a date.

Get percent of year complete

Formula

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

Explanation

The YEAR function returns a year for a selected date.

The DATE function returns a date for the selected day, month and year. In our case, YEAR(date) will be a year parameter for the function. The day and month parameters will be 1, as we want to calculate the percentage from the start of a year (1-Jan).

The YEARFRAC function returns a percentage of a year passed. The parameters of the function are Start_date – which is the start of the period for which we want to calculate a percentage. In our case, that is the result of DATE function (1-Jan of dates year); date, which is selected date.

Example 1

In the following example, we will see how to get a percent of the year complete for a date. Let’s see how the formula looks like:

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

 

Figure 1. Get percent of year complete

 

As you can see in the picture, in column B (“Date”), we have dates for which we want to calculate the percent of year complete. In the column C (“Percent of year complete”) we want to get the percentage of the year complete.

In the example, we want to get the project end date based on cells B3 and put it in the cell C3. First, function YEAR(B3) will return 2018, which is the year for date 25-Jan-18. This year will be the parameter for DATE function (2018, 1, 1), which will, therefore, return 1-Jan-18. This date will be start date parameter for YEARFRAC (1-Jan-18, 25-Jan-18). The result in C3 is 7%, which is the percentage of the completed year on date 25-Jan-18.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I'm calculating percent complete and percent remaining in excel. How would I do that?
Solved by Z. Y. in 17 mins
I have the per day value to calculate automatically. if the interest rate is 60 percent per year how to get the same result ?
Solved by A. B. in 21 mins
I have rows of data in a project database with columns for project forecast finish date and percent complete from 0% to 100%. I need to provide report which shows the rows of data for the projects with either forecast finish date in the next six weeks from today, or with percent complete greater than 0% and less than 100%
Solved by F. D. in 14 mins

Leave a Comment

avatar