How to Calculate Percentage Complete Based on Dates in Excel

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a powerful software. We can perform numerous tasks on datasets using Excel tools and features. There are many default Excel functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we must find out the percentage of completion of a particular project or task to track the work progress. In such cases, we have to deal with percentages. The percentage is usually expressed with a % sign. This article will show you 4 easy ways to calculate the Percentage Complete Based on Dates in Excel.


Download Practice Workbook

Download the following workbook to practice by yourself.


4 Easy Ways to Calculate Percentage Complete Based on Dates in Excel

A starting and an ending date are specifically mentioned for any project. We can determine the percentage of completion based on these two dates. However, we’ll need a date for reference, up to which we’ll compute the percentage complete. In this regard, we’ll use the TODAY function in this article. This function returns the current date. You can input any date you wish. To illustrate, we’ll use the following dataset as an example. For instance, the dataset contains Tasks, Start Dates, and End Dates. Now, go through the methods to calculate Percentage Complete Based on Dates in Excel.

excel percentage complete based on dates


1. Combine DATEDIF and TODAY Functions to Calculate Percentage Complete Based on Dates

The DATEDIF function can determine the number of days, months, and years between the two dates that we’ll insert in the function argument. In our first method, we’ll combine the DATEDIF and TODAY functions to create a formula. That formula will calculate the percentage complete. Therefore, follow the steps below to perform the task in Excel.

STEPS:

  • First, select cell E5.
  • Then, type the formula:
=(DATEDIF(C5,TODAY(),"d")+1)/(DATEDIF(C5,D5,"d")+1)
  • Subsequently, press Enter.
  • After that, use AutoFill.
  • Thus, you’ll get the formula outputs as shown below.

Combine DATEDIF and TODAY Functions to Calculate Percentage Complete Based on Dates in Excel

NOTE: DATEDIF(C5,TODAY(),”d”)+1 part of the formula finds out the number of days between the start date and today. Here, we add 1 to include today’s date. Next, DATEDIF(C5,D5,”d”)+1 determines the total duration of the project. Afterward, we divide the formula outputs to get the ratio of completion.
  • Now, select the range E5:E8.
  • Next, go to Home > Number and choose Percentage.

  • As a result, it’ll return the accurate percentage of completion.
  • See the figure below to understand better.


2. Determine Percentage Complete Based on Dates by Nesting MIN, DATEDIF, and TODAY Functions

However, if today is past the end date, the previous formula will give a bizarre result. See the following picture where we demonstrate the issue. For this reason, we need to update the formula to avoid such unwanted errors. We’ll apply the MIN function, which can give us the desired result. This function compares the numbers in its argument and returns the minimum. Here, we’ll combine the MIN, DATEDIF, and TODAY functions to compute the precise percentage complete. So, learn the below steps to carry out the operation.

Determine Percentage Complete Based on Dates by Nesting MIN, DATEDIF, and TODAY Functions

STEPS:

  • Firstly, click cell E5.
  • After that, insert the formula:
=MIN((DATEDIF(C5,TODAY(),"d")+1)/(DATEDIF(C5,D5,"d")+1),100%)
  • Accordingly, hit Enter and use AutoFill.
  • Hence, you’ll get the desired outcomes.

NOTE: DATEDIF(C5,TODAY(),”d”)+1)/(DATEDIF(C5,D5,”d”)+1) part of the formula returns the ratio of task completion as we described in the previous method. The MIN function lastly compares the formula output with 100% to avoid returning numbers that are greater than 100%. Anything that is 100% or above means the task is completed.

3. Create Formula Using Excel MIN and TODAY Functions for Calculating Percentage Complete

Moreover, the MIN and TODAY functions can also do the same job of determining the percentage complete. The formula is smaller too. Hence, follow the process below.

STEPS:

  • First of all, select cell E5.
  • Next, input the formula:
=(MIN(TODAY(),D5)-C5+1)/(D5-C5+1)
  • Subsequently, press Enter.
  • Apply AutoFill to complete the rest.
  • In this way, you can get the percentage of completion.

Create Formula Using Excel MIN and TODAY Functions for Calculating Percentage Complete

NOTE: MIN(TODAY(),D5) returns the output which is smaller between the current date and the ending date. Finally, (MIN(TODAY(),D5)-C5+1)/(D5-C5+1) formula calculates the ratio.

4. Get Percentage Complete of Month from Combination of DAY and EOMONTH Functions

We’ll assume that the duration of each of the tasks below is a month in our last example. The given Date is the reference up to which the work is completed. The DAY function extracts the day from a date and the EOMONTH function finds out the last date of a specified month. To calculate the percentage complete, we’ll join these two functions. Therefore, learn the following process.

Get Percentage Complete of Month from Combination of DAY and EOMONTH Functions

STEPS:

  • Firstly, choose cell D5.
  • Here, input the formula:
=DAY(C5)/DAY(EOMONTH(C5,0))
  • Press Enter.
  • Next, apply AutoFill to fill the series.
  • Thus, it’ll return the percentage of completion.
  • Look at the picture below where we demonstrate the results.

NOTE: DAY(C5) brings out the day from the C5 date, and DAY(EOMONTH(C5,0) extracts the day number from the last date of the C5 cell month. Then, we divide the function outputs to get the ratio of completion.

Conclusion

Henceforth, you will be able to calculate Percentage Complete Based on Dates in Excel using the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Aung Shine

Aung Shine

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

4 Comments
  1. Hello, I have a spreadsheet where I log completed training of a large number of people and teams. Each team has it’s own spreadsheet. There are a number of courses each person has to complete. I have set the spreadsheet up to make the cell go red if the person is out of date on a course and needs to repeat it and green if they are in date. How can I show the percentage completed for each course both for each team and again for the organisation as a whole?

    • Reply Avatar photo
      Saquib Ahmad Shuvo Mar 21, 2023 at 4:34 PM

      Hello SAZ,
      Greetings. Using Excel formulas and charts, you can display the percentage of each course that has been completed for each team and the entire organization. The steps are as follows:

      1) Calculate the percentage completed for each course for each person:

      You can use a formula to count the number of completed courses and divide it by the total number of courses for each person. For example, if the number of courses is in column B and the completed courses are in column C, the formula in column D could be:

      =IFERROR(C2/B2,0)
      Calculate the percentage completed for each course for each team:

      2) You can use the AVERAGEIF function to calculate the average percentage completed for each course for each team. For example, if the team name is in column A and the percentage completed is in column D, the formula in column E could be:

      =IFERROR(AVERAGEIF(A:A,A2,D:D),0)
      Calculate the percentage completed for each course for the organization as a whole:

      You can use the same AVERAGEIF function to calculate the average percentage completed for each course for the entire organization. For example, if the percentage completed is in column D, the formula in column E could be:

      =IFERROR(AVERAGEIF(D:D,”>0″),0)
      Create a chart:

      3) You can create a column chart to visualize the percentage completed for each course for each team and for the organization as a whole. Select the data range that includes the course names and the percentage completed columns, then go to Insert > Charts > Column Chart and choose the type of chart you prefer. You can also add labels, titles, and legends to the chart as needed.

      With the help of these steps, you ought to be able to monitor the percentage of each course that has been completed by each team and the organization as a whole.

  2. Hello, I’m trying to insert the formulas in excell, but it doesn’t work, I get this error message: “There is a problem with this formula.” I’ve tried to insert the dates as dates and texts, but the same error appears. Any idea how I can solve it?

    • Reply Avatar photo
      Shahriar Abrar Rafid Mar 21, 2023 at 5:59 PM

      Hello TANJA,
      Hope you are doing good. Thanks for your query.
      specifically, which method’s formula isn’t working for you? If you said that, I could understand well. Because all formulas are working on my PC.
      The error message “There is a problem with this formula” usually appears when there is an issue with the syntax of the formula. If we take Method 2 as example, the following is the formula we used in cell E5:

      =MIN((DATEDIF(C5,TODAY(),"d")+1)/(DATEDIF(C5,D5,"d")+1),100%)

      It’s possible that the issue is related to the regional settings or the version of Excel being used on your PC. The formula we are using includes a percentage sign (“%“), which represents a percentage value. Depending on the regional settings or version of Excel being used, the formula may not recognize the percentage sign as a valid argument. To avoid any potential compatibility issues, you can modify the formula to use a decimal value instead of a percentage. For example, you can use “1” to represent 100%. I just told it as an example. It could be better if you tell me which formula isn’t working and which Excel version are you using.
      Hope you can understand me. Happy Excelling.

      Regards,
      SHAHRIAR ABRAR RAFID
      Team ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo