How to Calculate Missing Work Time in WPS Spreadsheet Without Showing Hours for Blank Rows
Question details
The user needs a formula to calculate missing work time from a standard four-hour work session, while displaying zero for rows that have no start or finish time.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet tracks start and finish times for a standard four-hour work session, and the user wants the missing duration calculated only when both times are present.
- Observed behavior
- Rows without a start or finish time should display zero instead of the full four-hour session length.
Confirm that start and finish times are in separate columns and that the standard session length is stored in a cell you can reference, such as B1.
Use IF and OR to Return Zero for Blank Start or Finish Cells
Use this formula when rows without start or finish times should show zero instead of the full four-hour session length.
The OR function checks whether either the start or finish cell is blank. If either is blank, IF returns 0. When both times are present, the formula subtracts the elapsed time from the standard session length and MAX prevents negative results.
Click cell C4, or the first row where you want the missing work time result to appear.
Type =IF(OR(A4:B4=""),0,MAX($B$1-(B4-A4),0)) and adjust A4:B4, A4, B4, and $B$1 to match your time columns and standard session length cell.
Press Enter, then drag the fill handle from the lower-right corner of C4 down through the remaining rows.
Calculate Missing Work Time in WPS Spreadsheet with an IF Formula
Use WPS Spreadsheet to apply the IF and OR formula, fill it down, and return zero for blank time rows while preserving standard session length calculations.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook that contains your start and finish time columns.
- 2. Select the result cell: Click the first cell where the missing work time should appear, such as C4.
- 3. Enter the formula: Type =IF(OR(A4:B4=""),0,MAX($B$1-(B4-A4),0)) and adjust the cell references to match your worksheet.
- 4. Fill the formula down: Press Enter, then drag the fill handle down through all rows that need the calculation.
- 5. Check blank rows: Confirm that rows without start or finish times now display 0 instead of the full session length.
Frequently Asked Questions
What does the OR(A4:B4="") part of the formula do?
It checks whether either A4 or B4 is empty. If at least one cell is blank, the IF function returns 0 so the row does not show the full four-hour session length.
Can I use this formula if my standard session length is not in B1?
Yes. Replace $B$1 with the cell that holds your standard session length, or type the length directly into the MAX formula if it is always the same value.
How do I apply the formula to many rows at once?
Enter the formula in the first result cell, press Enter, then drag the fill handle from the lower-right corner of the cell down through the remaining rows.
Why does the formula include MAX with 0?
The MAX function prevents negative work time. If the elapsed time is greater than the standard session length, the result is 0 rather than a negative number.
