Return a Blank in WPS Spreadsheet When the Actual Finish Date Is Missing
Question details
The user needs a status formula to return a blank result when the Actual Finish date has not been entered.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing an Actual Finish date in cell C2 with a planned date in cell B2 to label an item as on time or late.
- Observed behavior
- A blank Actual Finish cell is treated as a date value during the comparison, causing incomplete items to be incorrectly marked as on time.
Confirm that B2 contains the planned date and C2 is the Actual Finish date. Enter the formula in the status cell for the same row, then fill it down only after checking the cell references.
Check for a blank Actual Finish date before comparing dates
Use this formula when a task should show no status until an Actual Finish date is available.
The first IF test checks whether C2 is empty. If it is empty, the formula returns an empty string; otherwise, it compares the actual finish date with the planned date.
In WPS Spreadsheet, click the cell where you want the On Time or Late result to appear, such as D2.
Type or paste =IF(C2="","",IF(C2<B2,"on time","late")) and press Enter.
Select the formula cell and drag the fill handle down the column to apply the same logic to additional task rows.
Clear the date in C2 to confirm the status cell becomes blank, then enter a finish date to display on time or late.
Keep task status blank until a finish date is entered in WPS Spreadsheet
WPS Spreadsheet lets you combine IF conditions to prevent unfinished tasks from being classified too early. Add a blank-cell check first, then compare the actual and planned finish dates only when a valid date is entered.
- 1. Open the schedule workbook: Open your task tracker in WPS Office and select the worksheet containing the planned and actual finish date columns.
- 2. Identify the date columns: Verify that the planned date is in column B and the Actual Finish date is in column C, or adjust the formula references for your sheet.
- 3. Add the status formula: Click the first status cell, enter =IF(C2="","",IF(C2<B2,"on time","late")), and press Enter.
- 4. Copy the formula to task rows: Drag the fill handle from the formula cell down the status column to calculate results for the remaining tasks.
Frequently Asked Questions
Why does a blank Actual Finish cell show as on time?
Without a blank check, a date comparison can treat an empty cell as a zero-like value. Add IF(C2="","",...) before the date comparison so no status is returned until C2 has a date.
How do I show "late" when the Actual Finish date is the same as the planned date?
Use =IF(C2="","",IF(C2<B2,"on time","late")). Because the test uses C2<B2, a date equal to B2 returns late. If equal dates should be on time, use C2<=B2 instead.
Can I use different cells for the planned and actual finish dates?
Yes. Replace B2 and C2 with the relevant cells in your worksheet. For example, if the planned date is D2 and the actual date is E2, use =IF(E2="","",IF(E2<D2,"on time","late")).
How can I copy the formula to every task row?
Select the cell containing the formula in WPS Spreadsheet, then drag its fill handle downward along the status column. The row references will adjust automatically for each row.
