Calculation Issues

How to Keep Original Task Duration When Excel Dates Change in WPS Spreadsheets

Amos GikundaAmos Gikunda Nov 13, 2026 868 views

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.

How to Keep Original Task Duration When Excel 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 you start

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.

Solution 1Recommended

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.

1
Open the workbook and add forecast and actual columns

In WPS Spreadsheets, open your workbook and create six columns: Forecast Start, Forecast Finish, Forecast Duration, Actual Start, Actual Finish, and Actual Duration.

2
Enter forecast and actual dates

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.

3
Calculate the forecast duration

In the Forecast Duration cell, enter the formula =ForecastFinish-ForecastStart+1 for calendar-day duration, or =NETWORKDAYS(ForecastStart,ForecastFinish) for working days.

4
Calculate the actual duration

In the Actual Duration cell, enter =ActualFinish-ActualStart+1 or =NETWORKDAYS(ActualStart,ActualFinish). The original forecast duration remains unchanged even when actual dates change.

Store Forecast and Actual Dates in Separate Columns and Calculate Durations
Original duration is preserved: Because forecast and actual values are stored separately, changing actual start or finish dates only updates the Actual Duration column.
WPS Spreadsheets solution

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. 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. 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. 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. 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.
Keeps original task duration separate from actual schedule changesUses Excel-compatible formulas such as =ActualFinish-ActualStart+1 and NETWORKDAYSFully compatible with Microsoft Excel .xlsx and .xls filesFree, lightweight, and familiar spreadsheet interfaceWorks on Windows, macOS, Android, and iOS
microsoft office alternative - wps office

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.