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.
How to Calculate Percentage Complete Based on Dates in Excel: 4 Easy Ways
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.
- 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.
- 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).
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.
- First, select cell E5.
- Then, type the formula:
- Subsequently, press Enter.
- After that, use AutoFill.
- Thus, you’ll get the formula outputs as shown below.
- 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.
- Firstly, click cell E5.
- After that, insert the formula:
- Accordingly, hit Enter and use AutoFill.
- Hence, you’ll get the desired outcomes.
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.
- First of all, select cell E5.
- Next, input the formula:
- Subsequently, press Enter.
- Apply AutoFill to complete the rest.
- In this way, you can get the percentage of completion.
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.
- Firstly, choose cell D5.
- Here, input the formula:
- 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.
Things to Remember
When inserting date values within the start date and end date columns, ensure all the dates are in the same date format.
Download Practice Workbook
Download the following workbook to practice by yourself.
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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
- How to Calculate Total Percentage from Multiple Percentages in Excel
- How to Calculate Percentage of Month in Excel
- How to Calculate Percentage of Percentage in Excel
- How to Calculate Percentage Based on Conditional Formatting
- How to Calculate Percentage in Excel Based on Cell Color
- Percentage Showing as Thousand in Excel
- Why Are My Percentages Wrong in Excel?
- How to Remove Percentage in Excel
- How to Find the Percentage of Two Numbers in Excel
- How to Calculate Error Percentage in Excel
- How to Calculate Cumulative Percentage in Excel