Display Excel Date-Time Differences Greater Than 24 Hours in WPS Office Spreadsheets
Question details
The user needs to subtract two date-time values in WPS Office Spreadsheets and display the result as total elapsed hours, such as 82:55:00, rather than having the hour value reset after 24 hours.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with date-time values that span multiple days and calculating the exact duration between them.
- Observed behavior
- Standard time formats reset the hour value after 24 hours, so a multi-day difference does not show total elapsed hours correctly.
Open the WPS Office spreadsheet that contains the start and end date-time values, and confirm that both cells use a recognized date-time format rather than plain text.
Use a Custom [h]:mm:ss Format to Show Elapsed Time Over 24 Hours
Use this when the result of subtracting two date-time cells must remain a time value and display total elapsed hours such as 82:55:00.
WPS Office Spreadsheets stores date-time subtraction results as days plus a fractional day. The square-bracket [h] code tells WPS to keep counting hours beyond 24 instead of resetting to 0.
Click the cell where you want the date-time difference to appear, for example C1.
Type =B1-A1 and press Enter, where B1 is the later date-time and A1 is the earlier date-time.
With the result cell selected, press Ctrl+1 or right-click and choose Format Cells.
In the Number tab, select Custom, enter [h]:mm:ss in the Type box, and click OK.
![Use a Custom [h]:mm:ss Format to Show Elapsed Time Over 24 Hours](https://res-academy.cache.wpscdn.com/upload/seo-engine-admin/2026-11-12/4aadc99363e703b23e215b1976b571fd.png)
Display the Difference as Decimal Hours for Further Calculation
Use this if you need the duration as a numeric value, for example to multiply by an hourly rate or total several durations.
Calculate Elapsed Time Over 24 Hours in WPS Office Spreadsheets
WPS Office Spreadsheets lets you subtract date-time cells and apply a custom [h]:mm:ss format to show total elapsed hours that continue past 24 instead of resetting.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the workbook containing the start and end date-time values.
- 2. Enter the difference formula: In a blank cell, type =B1-A1, replacing B1 and A1 with your later and earlier date-time cells, and press Enter.
- 3. Open Format Cells: Select the result cell, press Ctrl+1, and go to the Number tab.
- 4. Apply [h]:mm:ss: Choose Custom, enter [h]:mm:ss, and click OK to display the total elapsed hours.
Frequently Asked Questions
Why does my Excel date-time difference reset to zero after 24 hours?
Standard time formats such as h:mm:ss display clock time, so they reset every 24 hours. Use the custom format [h]:mm:ss in WPS Office Spreadsheets to show total elapsed hours instead.
What does [h]:mm:ss do in WPS Office Spreadsheets?
The square brackets around h tell WPS Office Spreadsheets to keep counting hours beyond 24 instead of rolling over to 0 after one full day.
How do I avoid a negative time result when subtracting dates?
Always subtract the earlier date-time from the later date-time. For example, if B1 is the later value and A1 is the earlier value, use =B1-A1.
Does the [h]:mm:ss custom format work when the file is opened in Microsoft Excel?
Yes, the [h]:mm:ss custom number format is compatible with Excel .xlsx files, so the elapsed time displays correctly in both WPS Office and Microsoft Excel.
Can I show total hours as a number instead of a time value?
Yes. Use the formula =(B1-A1)*24 and format the result as a general number or number with decimal places to get the duration in decimal hours.
