Fix Excel Time Formula to Ignore Text Such as PTO and Return Zero Hours in WPS Spreadsheets
Question details
The user needs to adapt a WPS Spreadsheets time-calculation formula so text entries such as PTO, HOL, IN, and OFF do not trigger errors and are treated as zero hours.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing a time sheet or attendance workbook where shift start/end cells may contain text codes like PTO, HOL, IN, or OFF instead of actual times.
- Observed behavior
- The current time-calculation formula produces an error when it subtracts or compares text cells, breaking total and overtime calculations.
Open the workbook in WPS Spreadsheets and identify the time-pair cells, for example C10 and B10, and the total-hours cell that contains the formula. Confirm which cells can contain text codes such as PTO, HOL, IN, or OFF so you wrap every relevant time calculation.
Wrap Each Time Pair in IFERROR to Return Zero Hours
Use this when the existing formula works for real times but returns #VALUE! or other errors for text entries such as PTO, HOL, IN, or OFF.
This method keeps your current shift-length and overtime logic intact. IFERROR catches the text-related error and returns 0 for that time pair, so text codes contribute zero hours instead of breaking the total.
Click the cell that contains the time calculation, for example S10, then click in the formula bar or press F2 to edit it.
Type =IFERROR( immediately before the existing calculation and add ,0) at the end. For example: =IFERROR(IF(C10-B10>TIME(5,0,0),C10-B10-TIME(0,30,0),C10-B10),0).
For each additional time-pair expression in the formula, wrap it with IFERROR(...,0) so text codes such as PTO or HOL return zero hours.
Press Enter, select the result cell, press Ctrl+1, choose Time, and pick a suitable time format so the returned value displays correctly.
If S10 holds the total time, enter =TEXT(S10*24,"0.00") in P10 to get numeric hours, then reference P10 or the same numeric conversion in Q10 for overtime.
Pre-check Each Input with IF and ISTEXT Before Subtracting
Use this if you prefer to test each start/end cell explicitly before performing the time calculation.
Create a Text-Safe Time Sheet Formula in WPS Spreadsheets
Use WPS Spreadsheets to open your existing time sheet and apply IFERROR wrappers so PTO, HOL, IN, and OFF entries return zero hours. WPS handles the same Excel formulas and formatting while keeping the workbook compatible with Microsoft Excel files.
- 1. Download and install WPS Office: Go to the official WPS Office website, download the free Windows/macOS/Linux version, and install WPS Spreadsheets.
- 2. Open your Excel time sheet: Launch WPS Spreadsheets, click Menu > Open, and select the .xlsx or .xls workbook that contains the time formulas.
- 3. Edit the formula and apply IFERROR: Select the total-hours cell, click the formula bar, and wrap each time calculation with IFERROR(...,0) using the pattern described above.
- 4. Save in Excel-compatible format: Click Menu > Save As, choose Excel Workbook (.xlsx), and keep the file ready for Microsoft Excel or WPS Office.
Frequently Asked Questions
Why does my time formula show #VALUE! when a cell contains PTO?
Because subtracting or comparing text such as PTO with a time value is not a valid numeric operation. Wrap the operation in IFERROR(...,0) or use ISTEXT to test the cell and return 0.
How do I make PTO, HOL, IN, and OFF all count as zero hours in a weekly total?
Wrap each daily time calculation in IFERROR(...,0). For example: =IFERROR(IF(C10-B10>TIME(5,0,0),C10-B10-TIME(0,30,0),C10-B10),0), then sum the daily results.
Can WPS Spreadsheets use the same IFERROR formula as Excel?
Yes. WPS Spreadsheets supports IFERROR, TIME, TEXT, and other Excel functions, so you can use the same formulas in both applications.
How do I display total hours as a decimal number after the formula is fixed?
If S10 contains the total time, enter =TEXT(S10*24,"0.00") in another cell. This converts a time value to numeric hours such as 8.50.
What format should the result cell use?
Select the cell, press Ctrl+1, choose Time, and pick a format such as 13:30 or 37:30:55. For decimal hours, use the TEXT formula or number format instead.
