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.