How to Calculate Cumulative Stop Times from Durations in WPS Office Spreadsheets
Question details
The user needs to calculate cumulative stop times by adding a starting time to a series of duration values so each row displays the next running stop time.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a start time in one cell and duration values in a column, and wants to create a running cumulative stop time column such as 8:00 AM plus 2:13 resulting in 10:13 AM.
- Observed behavior
- The duration values are present, but the cumulative stop times have not yet been calculated row by row.
Make sure the starting time and duration cells contain real time values rather than text. Format them as h:mm or [h]:mm first so the formulas return the correct cumulative times.
Calculate Cumulative Stop Times with Running Total Formulas
Use this method for all versions of WPS Office Spreadsheets and Excel because it uses simple cell references and the fill handle.
This formula method locks the start time and adds each duration in sequence, so each result row shows the total time elapsed up to that row.
Type the starting time, such as 8:00 AM, in a cell like A2, then press Ctrl+1 and apply a Time format.
Put each duration, such as 2:13, in column B starting at B2. Select the duration cells, press Ctrl+1, and choose h:mm or [h]:mm.
In the first result cell C2, enter =$A$2+B2. This adds the locked start time to the first duration.
In C3, enter =C2+B3, then use the fill handle to copy the formula down through the remaining rows.
Select the result column, press Ctrl+1, choose Time, and apply a format such as h:mm AM/PM.
Use SCAN to Calculate Cumulative Stop Times in One Formula
Use this in Excel 365 or WPS Office versions that support the SCAN function to spill all results automatically without filling down.
Calculate Cumulative Stop Times Directly in WPS Office Spreadsheets
Use WPS Office Spreadsheets to store start and duration cells as real time values, then build a running total formula or SCAN formula to return each cumulative stop time without switching software.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click File, select Open, and choose the workbook containing your start time and duration columns.
- 2. Confirm time formats: Select the start and duration cells, press Ctrl+1, choose Time, and make sure they are real time values rather than text.
- 3. Enter the cumulative formula: In the first result cell, type =$A$2+B2. In the next row, type =C2+B3 and drag the fill handle down through the remaining rows.
- 4. Format the result column: Select the result column, press Ctrl+1, and apply the h:mm AM/PM time format.
- 5. Review the running stop times: Check that each row displays the expected cumulative stop time based on the durations and the starting time.
Frequently Asked Questions
Why does my duration addition show a decimal or date instead of a time?
Usually the cells are not formatted as time or the duration is stored as text. Select the cells, press Ctrl+1, set the start and duration cells to h:mm or [h]:mm, and set the result cells to h:mm AM/PM. If a value has an apostrophe or is left-aligned as text, retype it as a real time value.
How do I keep the start time fixed when copying the formula down?
Use an absolute reference such as $A$2 for the start time. The dollar signs lock that cell while the duration reference changes as you fill the formula down.
When should I use SCAN instead of a fill-down formula?
SCAN is useful in Excel 365 or WPS versions that support dynamic arrays because it returns all cumulative results automatically from one formula. If SCAN is not available, the running total formula with fill down works in all versions.
Can durations exceed 24 hours in a cumulative stop time?
Yes, but you should use a time format such as [h]:mm for durations and cumulative totals. Otherwise the display may wrap to the next day after 24 hours and show only the remaining time.
