Calculate Estimated Days Remaining from Project Completion Percentage in WPS Spreadsheet
Question details
The user needs to estimate a project end date from a start date and the current completion percentage using WPS Spreadsheet.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user tracks a project by its start date and current completion percentage and needs to estimate the project end date.
- Observed behavior
- The desired result is an estimated completion date in a worksheet cell that recalculates when the completion percentage changes.
Check that your start date is a recognized date value and your completion percentage is formatted as a percentage. Avoid using the formula when the completion percentage is zero, or wrap it with an IF condition to prevent a #DIV/0! error.
Calculate the Estimated Completion Date with a Date Formula
Use this formula when you have a start date and a non-zero completion percentage and want an estimated project end date in WPS Spreadsheet.
The formula treats the completion percentage as progress over elapsed time. It projects the total duration from the start date to today and adds that duration to the start date.
Click an empty cell, such as E100, where you want the estimated completion date to appear.
Type =D100+100%/C100*(TODAY()-D100) and press Enter, where D100 is the start date and C100 is the completion percentage.
Select the formula cell, click the Home tab, open the Number Format drop-down, and choose Short Date or Long Date.
Return a Blank When the Completion Percentage Is Zero
Use this version when the completion percentage may be empty or zero and you want to avoid #DIV/0! errors.
Calculate Project Completion Dates in WPS Spreadsheet
Use WPS Spreadsheet to estimate the remaining days and project end date with a simple formula. The workbook recalculates the estimate automatically as you update the completion percentage.
- 1. Open your workbook: Launch WPS Office and open the workbook that contains the project start date and completion percentage.
- 2. Enter the completion date formula: Click an empty cell, type =D100+100%/C100*(TODAY()-D100), replace D100 and C100 with your actual cell references, and press Enter.
- 3. Format the result as a date: Select the formula cell, click Home, open the Number Format drop-down, and choose Short Date or Long Date.
- 4. Update the percentage: Change the completion percentage in C100; WPS Spreadsheet recalculates the estimated completion date immediately.
Frequently Asked Questions
Why does the estimated completion date change when the completion percentage changes?
The formula uses TODAY()-D100 as elapsed time and divides it by the completion percentage. When C100 changes, the projected total duration changes, so the estimated end date recalculates automatically in WPS Spreadsheet.
What should I do if my percentage cell is zero?
Do not use the basic formula with a zero percentage because it can cause a #DIV/0! error. Instead, use =IF(C100=0,"",D100+100%/C100*(TODAY()-D100)) and replace D100 and C100 with your actual cell references.
Can I use this project completion formula in Microsoft Excel?
Yes, the formula syntax is compatible with Microsoft Excel and WPS Spreadsheet. Both support TODAY(), percentage division, and date arithmetic, so the same .xlsx workbook can be opened in either application.
How do I format the estimated completion date as a date?
Select the formula cell, click the Home tab, open the Number Format drop-down, and choose Short Date or Long Date. The formula result then displays as a date instead of a serial number.
