How to Fix WPS Spreadsheets Time Formulas Returning 0:00 or -0:00
Question details
The user needs to prevent time-difference formulas from displaying 0:00 or -0:00 instead of a blank or clean zero value, especially when source times look identical but contain hidden binary differences.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Subtracting or comparing time values in a worksheet and expecting an exact zero duration to remain blank or display correctly.
- Observed behavior
- Formulas such as H2-W2 return 0:00 or -0:00 because underlying binary time differences can make H2>W2 evaluate as TRUE even when the displayed times match.
Open the affected workbook and identify the source time cells and result cells. Confirm the result cells use a time format such as [h]:mm, because formatting affects the displayed value but not the underlying binary time value.
Round Time Calculations to Minute Precision Before Comparing or Displaying
Use this when a time formula such as H2-W2 shows 0:00 or -0:00 even though visible times match; rounding to the required precision removes hidden binary residue.
WPS Spreadsheets and Excel store time as fractional days. Subtracting times that look identical can leave tiny binary differences that compare as TRUE or display as an unexpected zero sign.
Click the cell where the time difference currently returns 0:00 or -0:00.
Type =--TEXT(G2-F2,"[h]:m") and press Enter. Replace G2 and F2 with your actual end-time and start-time cells.
Go to Home > Number Format, select Custom, enter [h]:mm, and click OK so the rounded value displays as time.
Change any adjacent comparison, such as H2>W2, to reference the rounded result cell instead of the raw time difference.
Check the 1904 Date System for Negative Zero Display Issues
Use this when negative zero values appear after opening workbooks exchanged between Mac and Windows systems or between different spreadsheet applications.
Calculate Reliable Time Differences in WPS Spreadsheets
Use WPS Spreadsheets to round time calculations to minute precision, remove hidden binary time drift, and display or hide zero durations with familiar Excel-compatible formulas.
- 1. Open the workbook: Launch WPS Spreadsheets and open the .xlsx file containing the affected time formulas.
- 2. Confirm the result cell format: Click the cell displaying 0:00 or -0:00 and go to Home > Number Format to check that it uses a time format such as [h]:mm.
- 3. Enter the rounded formula: Type =--TEXT(G2-F2,"[h]:m") and press Enter, replacing G2 and F2 with your actual time cells.
- 4. Apply the custom time format: With the result cell selected, go to Home > Number Format > Custom, enter [h]:mm, and press OK.
- 5. Review dependent comparisons: Update comparison formulas such as H2>W2 to reference the rounded time cell so they do not react to binary residue.
Frequently Asked Questions
Why does WPS Spreadsheets show -0:00 when the displayed times are exactly the same?
Time values are stored as fractional days, and subtraction can leave tiny binary remainders that round for display but not for comparison. Use =--TEXT(G2-F2,"[h]:m") and format the result as [h]:mm to remove that remainder.
How do I make a zero time difference return a blank cell instead of 0:00?
After creating a rounded time result, wrap the formula in IF, for example =IF(--TEXT(G2-F2,"[h]:m")=0,"",--TEXT(G2-F2,"[h]:m")). This returns an empty string for exact zero minutes.
Does the 1904 date system affect time calculations?
Yes. Workbooks using the 1904 date system can show negative zero values when opened on systems using the 1900 system. In Excel, check File > Options > Advanced > 'Use 1904 date system' and adjust it consistently.
Will this formula work in both WPS Spreadsheets and Microsoft Excel?
Yes. WPS Spreadsheets supports Excel-compatible formulas such as TEXT and the custom time format [h]:mm, so the same workbook and formulas can be used across both applications.
What does [h]:m mean in the TEXT formula?
It extracts elapsed hours and minutes as text. The double negative -- converts that text back to a numeric time value, which rounds the result to the minute.
