How to Keep Original Task Duration When Excel Dates Change in WPS Spreadsheets
Question details
The user wants to keep a task’s original planned duration unchanged while separately calculating actual duration after start or finish dates change.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing project or task schedules in a spreadsheet when planned dates may shift and actual dates are recorded separately.
- Observed behavior
- The original planned duration remains stored in dedicated forecast columns while actual duration can update independently from actual date columns.
Before adding formulas, create separate columns for Forecast Start, Forecast Finish, Forecast Duration, Actual Start, Actual Finish, and Actual Duration. Make sure all date columns are formatted as Date so subtraction and NETWORKDAYS results are correct.
Store Forecast and Actual Dates in Separate Columns and Calculate Durations
Use this method when you need the planned duration to remain fixed while actual duration reflects schedule changes.
The key is to avoid overwriting the original forecast. By keeping forecast and actual values in separate columns, the planned duration stays intact and can be compared with the actual duration.
In WPS Spreadsheets, open your workbook and create six columns: Forecast Start, Forecast Finish, Forecast Duration, Actual Start, Actual Finish, and Actual Duration.
Type the planned dates in the Forecast Start and Forecast Finish cells, and enter the real dates in Actual Start and Actual Finish once they are known.
In the Forecast Duration cell, enter the formula =ForecastFinish-ForecastStart+1 for calendar-day duration, or =NETWORKDAYS(ForecastStart,ForecastFinish) for working days.
In the Actual Duration cell, enter =ActualFinish-ActualStart+1 or =NETWORKDAYS(ActualStart,ActualFinish). The original forecast duration remains unchanged even when actual dates change.

Calculate Working-Day Duration with NETWORKDAYS Instead of Calendar Days
Choose this approach when task duration should exclude weekends or holidays and reflect only business days.
Track Planned and Actual Task Durations Accurately in WPS Spreadsheets
Use WPS Spreadsheets to set up forecast and actual date columns and apply Excel-compatible formulas so your original task duration stays unchanged while actual duration updates independently.
- 1. Open WPS Spreadsheets and prepare the workbook: Launch WPS Spreadsheets, open an existing .xlsx workbook or create a new workbook, and add the forecast and actual date columns.
- 2. Enter planned and actual dates: Fill the Forecast Start and Forecast Finish cells with the original plan, then enter actual start and finish dates in the Actual Start and Actual Finish cells.
- 3. Add duration formulas: In the Forecast Duration cell, type =ForecastFinish-ForecastStart+1 or =NETWORKDAYS(ForecastStart,ForecastFinish). In the Actual Duration cell, use the same formula with Actual Start and Actual Finish.
- 4. Verify and save the workbook: Check that the forecast duration remains unchanged when actual dates change, then save the workbook in WPS Spreadsheets or export it as an Excel .xlsx file.

Frequently Asked Questions
Why does my planned task duration change when I edit the start or finish date?
It usually happens because the duration formula references the same date cells that are being overwritten. Store the original forecast start and finish in separate columns and reference those cells in the forecast duration formula.
Which formula should I use for calendar-day task duration?
Use =ActualFinish-ActualStart+1 to include both the start and finish days in the count. The +1 prevents losing a day when subtracting dates.
How do I calculate only working days between two dates in WPS Spreadsheets?
Use =NETWORKDAYS(StartDate,EndDate) to exclude weekends. Add a third argument, such as =NETWORKDAYS(ActualStart,ActualFinish,$H$2:$H$10), to also exclude holidays.
Can I keep the original duration when actual dates are blank?
Yes. The forecast duration remains in its own column. If the actual duration formula references blank actual date cells, it may show a result or error; wrap it with IFERROR or IF to keep the cell blank until actual dates are entered.
Does WPS Spreadsheets support NETWORKDAYS and Excel date subtraction?
Yes, WPS Spreadsheets is compatible with common Excel date functions, including NETWORKDAYS, date subtraction, and cell formatting for Date, and it opens and saves .xlsx files.




