How to Calculate Blank OutTime and WaitTime in WPS Spreadsheet
Question details
User needs a formula strategy in WPS Spreadsheet to calculate elapsed wait time when the OutTime cell is blank without creating a circular reference.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is tracking InTime and OutTime in a table and wants WaitTime to show elapsed time even when OutTime has not been entered yet.
- Observed behavior
- Placing a formula in the OutTime column to fill its own blank cells creates a circular reference; WaitTime calculation fails or must be handled separately.
Confirm whether your data is formatted as a WPS Spreadsheet table with structured references such as [@OutTime]. If not, adjust the formula to use normal cell references like D2 and C2 before applying the steps.
Use a helper column for OutTime to avoid circular references
Best when you need a maintainable workbook and do not want formulas inside the same OutTime column.
A formula cannot return a value to the same blank cells it references, so keep the source OutTime column separate from any calculated column. A helper column stores either the actual OutTime or a formula that derives it from another source.
Click the column header immediately to the right of the OutTime column, then go to Home > Insert > Insert Sheet Columns in WPS Spreadsheet.
In the new column's first cell, type OutTime_Helper and press Enter.
In the helper column, type the actual OutTime values for each row, or enter a formula such as =IF(D2<>"",D2,TODAY()) if you intentionally want fallback values without touching column D.
Select the first WaitTime cell and enter =IF([@OutTime_Helper]="",TODAY(),[@OutTime_Helper])-[@InTime]. Press Enter and drag the fill handle down.
With the WaitTime range selected, go to Home > Number Format and choose General or Number with zero decimal places to display elapsed days as a number.
Calculate WaitTime with IF and TODAY when OutTime is blank
Use this direct formula when you only need the elapsed time calculation and can accept a dynamic result for blank OutTime cells.
Calculate Blank OutTime and WaitTime in WPS Spreadsheet Without Circular References
WPS Spreadsheet lets you create a separate helper column or use the IF and TODAY functions to calculate wait time safely. Open your Excel workbook, enter the formula, and format the result in seconds.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, click Menu > Open, and select your .xlsx or WPS Spreadsheet workbook.
- 2. Insert a helper column if needed: Select the column next to OutTime, right-click the header, choose Insert, and name it OutTime_Helper.
- 3. Enter the WaitTime formula: Click the first WaitTime cell and type =IF([@OutTime]="",TODAY(),[@OutTime])-[@InTime], then press Enter.
- 4. Format the result as a number: Select the WaitTime range, go to Home > Number Format, and choose General or Number.
- 5. Save the file: Press Ctrl+S and save the workbook in .xlsx or WPS format.
Frequently Asked Questions
Why does putting a formula in the OutTime column create a circular reference?
Because the formula would need to read the same OutTime cell it is trying to fill. WPS Spreadsheet detects this as a circular reference and cannot calculate a stable result; use a helper column or a separate WaitTime formula instead.
How do I make WaitTime show elapsed days instead of a date?
Select the WaitTime cells, go to Home > Number Format, and choose General or Number with zero decimal places. The value is a date serial difference, so a number format shows elapsed days correctly.
Can I use this formula if my data is not in a formatted table?
Yes. Replace [@OutTime] and [@InTime] with normal cell references, for example =IF(D2="",TODAY(),D2)-C2, and drag the formula down.
Is there a way to automatically fill blank OutTime cells without retyping data?
You can use a helper column with a formula such as =IF(D2<>"",D2,TODAY()) and reference that helper column for WaitTime. A direct formula in column D is not possible because it creates a circular reference.
