How to Display Elapsed Hours and Minutes in Separate Excel Cells in WPS Office
Question details
The user needs to calculate the elapsed time between start and finish times in cells O10 and P10, placing whole elapsed hours in Q10 and remaining minutes in R10 while returning blank for missing times, zero hours, or zero results.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains start and finish times in O10 and P10; the user wants the elapsed duration split into separate hour and minute cells.
- Observed behavior
- The expected result is to display blank cells when the input times are missing, when elapsed hours are zero, or when remaining minutes are zero.
Verify that O10 and P10 contain valid time values or are formatted as Time, and identify the result rows where the Q and R formulas will be copied.
Calculate elapsed hours and remaining minutes with blank-zero checks
Use this when you want total elapsed hours and remaining minutes in separate cells, and blank results for missing or zero values.
This formula approach tests the start and finish cells first, then uses INT and MOD to separate total hours from leftover minutes.
Select Q10, type =IF(OR(O10="",P10=""),"",IF(INT((P10-O10)*24)=0,"",INT((P10-O10)*24))) and press Enter.
Select R10, type =IF(OR(O10="",P10=""),"",IF(MOD((P10-O10)*1440,60)=0,"",MOD((P10-O10)*1440,60))) and press Enter.
Select Q10:R10, drag the fill handle down through the rows that contain start and finish times, or copy and paste into Q11:R13.
Use HOUR and MINUTE formulas for simple same-day durations
Use this for quick calculations when elapsed time is within the same day and you want a shorter formula.
Calculate Elapsed Hours and Minutes in WPS Office Spreadsheets
Calculate elapsed time from start and finish cells in WPS Office Spreadsheets using Excel-compatible formulas, with blank results for missing or zero values.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the .xlsx workbook containing start and finish times.
- 2. Select the elapsed hours cell: Click cell Q10 and enter the elapsed-hours formula in the formula bar.
- 3. Select the remaining minutes cell: Click cell R10 and enter the remaining-minutes formula in the formula bar.
- 4. Copy the formulas down: Select Q10:R10 and drag the fill handle down through Q13:R13 or as far as needed.
- 5. Check the displayed results: Confirm that valid time pairs show hours and minutes, while blank or zero results appear empty.
Frequently Asked Questions
Why does my formula show 0 instead of blank?
Wrap the result in an IF check that tests whether the calculated hours or minutes equal zero, or apply a custom number format such as [=0]"";General to the result cells.
Can I show total elapsed hours beyond 24 hours?
Yes. Use INT((P10-O10)*24) for total hours, because HOUR(P10-O10) resets after 24 hours.
What should I do if the start time is later than the finish time?
Use MOD(P10-O10,1) inside the calculation to handle times that cross midnight, for example =IF(OR(O10="",P10=""),"",INT(MOD(P10-O10,1)*24)).
How do I copy the formulas down to other rows?
Select Q10:R10, then drag the fill handle downward over Q11:R13, or copy the cells and paste them into the target range.
Do O10 and P10 need special formatting?
They should contain valid Excel time values or be formatted as Time. If they are text, the formulas may return an error or blank result.
