# How to Calculate Percentage Completed Based on Dates in Excel: 4 Methods

A starting and an ending date are needed. We’ll use the TODAY function for the current date. You can input any date. To illustrate, we’ll use the following dataset as an example..

Important Notes

Note that,

• If the TODAY function returns a date value between the start and end dates, you will get the intended percentage value.
• When TODAY is greater than the end date, we will get 100% or more than 100%, meaning the task is complete.
• If TODAY is less than both the start and end dates, we will get negative values or negative percentage values (which means the tasks have not started yet).

### Method 1 – Combining the DATEDIF and TODAY Functions to Calculate Percentage Completed Based on Dates

The DATEDIF function can determine the number of days, months, and years between the two dates to be inserted in the function argument. Method 1 combines the DATEDIF and TODAY functions to create a formula. .

STEPS:

1. Select cell E5.
2. Type the formula:
`=(DATEDIF(C5,TODAY(),"d")+1)/(DATEDIF(C5,D5,"d")+1)`
3. Press Enter.
4. Use AutoFill to populate the rest of the column.
5. 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.
6. Go to Home > Number and choose Percentage.

### Method 2 – Determine Percentage Complete Based on Dates by Nesting the MIN, DATEDIF, and TODAY Functions

If today is past the end date, the previous formula will give a bizarre result as seen  below. To avoid such unwanted errors use the MIN function. This function compares the numbers in its argument and returns the minimum.

To combine the MIN, DATEDIF, and TODAY functions to compute the precise percentage completed follow the steps below.

STEPS:

1. Click on cell E5.
2. Insert the formula:
`=MIN((DATEDIF(C5,TODAY(),"d")+1)/(DATEDIF(C5,D5,"d")+1),100%)`
3. Hit Enter and use AutoFill.
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.

### Method 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:

1. Select cell E5.
2. Input the formula:
`=(MIN(TODAY(),D5)-C5+1)/(D5-C5+1)`
3. Press Enter.
4. Apply AutoFill to complete the rest.
Formula Breakdown: MIN(TODAY(),D5) returns the smaller between the current date and the ending date. Finally formula, (MIN(TODAY(),D5)-C5+1)/(D5-C5+1) calculates the ratio.
NOTE: When you use this method, you may find 100% in the Percentage Completion column in the workbook provided. As mentioned earlier, we use the TODAY function as a reference date. 100% can be displayed because all the tasks are completed according to that day’s date.

### Method 4 – Get Percentage Completed Using the DAY and EOMONTH Functions

We’ll assume that the duration of each of the tasks below is a month. 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. .

STEPS:

1. Choose cell D5.
2. Input the formula:
`=DAY(C5)/DAY(EOMONTH(C5,0))`
3. Press Enter.
4. Apply AutoFill to fill the series.
5. 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.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

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?

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?

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

3. Hi – I have this formula for % completed between two dates in a project plan. I also added a iferror statement that if the dates are TBD it would return a 0%. Now I need to cap the percentages to 100%. Can you help with the right formula?

=IFERROR((DATEDIF(W6,TODAY(),”d”)+1)/(DATEDIF(W6,X6,”d”)+1),0%)

• Hlw Terrisa,
Thank you very much for your comment. The right formula for your need is given below-

=IFERROR(MIN((DATEDIF(W6,TODAY(),”d”)+1)/(DATEDIF(W6,X6,”d”)+1), 1), 0)

Here, we add the MIN function to cap the calculated percentage to 100%. If the calculated percentage is greater than 100%, this function will return 100%, capping the percentage. If the calculated percentage is less than or equal to 100%, it will return the calculated percentage as it is.

If you have other queries let me know in the comment.
Regards,
Sajid Ahmed
Exceldemy

4. Hello there!

I tried the formula Min-Today, but it’s giving the result 100%, always.
The formula has no error but the formula make is wrong. please note it.

Lutfor Rahman Shimanto Sep 12, 2023 at 5:24 PM

Thanks for reaching out and posting your comment. You are right about the Percentage Completion column showing 100%. Thank you once again for noticing the interesting issue. You are also correct about the formula used in the article having no error.

Reason: The Percentage Completion column shows 100% for each task. It is important to note that we are using TODAY as a reference day. All the tasks are completed 100% for the dataset shown in the article according to today’s dates.

Note:

1. If the TODAY function returns a date value between the start and end dates, you will get the intended percentage value.

2. When TODAY is greater than the end date, we will get 100% or more than 100%, meaning the task is complete.

3. In contrast, if TODAY is less than both the start and end dates, we will get negative values or negative percentage values (which means the tasks have not started yet).

Things to Remember:
When inserting date values within the start date and end date columns, ensure all the dates are in the same format.

Solution:
To demonstrate, I am using the dataset mentioned in this article. However, I will use some other date values. Here, you will see an improved version of the formula mentioned in the article to avoid negative values.

Select cell E5 >> apply the following formula >> drag Fill Handle to cell E8.

=IF(((MIN(TODAY(),D5)-C5+1)/(D5-C5+1))<0,0, ((MIN(TODAY(),D5)-C5+1)/(D5-C5+1)))

I hope the idea and concept are crystal clear to you now. Stay Blessed.

Regards
Lutfor Rahman Shimanto

Advanced Excel Exercises with Solutions PDF