Calculate session duration from start and end times in WPS Office Spreadsheets
Question details
The user needs to calculate the total duration of each session from start and end times, where a new session begins after a one-hour or greater gap between consecutive start times.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains session start and end times, and related rows must be grouped into sessions based on a gap of at least one hour between consecutive start times.
- Observed behavior
- The user wants to return the elapsed time from the first start time to the final end time for each session.
Ensure your start and end values are real Excel/WPS time values rather than text, and sort the rows chronologically by start time so the one-hour gap rule can be applied correctly.
Group rows by session number and calculate duration with MINIFS and MAXIFS
Use this method when a session can span multiple rows and you need the total elapsed time from the first start to the final end.
This method assigns a shared session number to rows that belong to the same session. MINIFS then returns the earliest start time for that session, MAXIFS returns the latest end time, and subtraction gives the session duration.
Insert a new column, for example column E, and enter the same session number for every row that belongs to the same session. Start a new number whenever the next start time is one hour or more after the previous start time.
In an empty cell next to the first session number, type =MINIFS($A$2:$A$100,$E$2:$E$100,E2) and press Enter. This returns the earliest start time from column A for the session number in E2.
In another empty cell, type =MAXIFS($D$2:$D$100,$E$2:$E$100,E2) and press Enter. This returns the latest end time from column D for that session number.
In the duration cell, enter =MAXIFS($D$2:$D$100,$E$2:$E$100,E2)-MINIFS($A$2:$A$100,$E$2:$E$100,E2), press Enter, then use Ctrl+1 and apply the custom format [h]:mm:ss.
Check the one-hour gap between a marked session end and the next start
Use this shortcut when you already mark session-end rows in column C and want a quick gap calculation to verify where a new session begins.
Calculate session duration in WPS Office Spreadsheets with MINIFS and MAXIFS
Open your workbook in WPS Office Spreadsheets, add a session number column, and use MINIFS and MAXIFS to calculate each session length from the first start time to the final end time. WPS Office handles .xlsx formulas and time formats without losing compatibility with Microsoft Excel.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the .xlsx workbook that contains your start and end times.
- 2. Add a session number column: Insert a helper column and assign the same session number to all rows in each session. Begin a new session number when the start-time gap is at least one hour.
- 3. Enter the duration formula: In the first duration cell, type =MAXIFS($D$2:$D$100,$E$2:$E$100,E2)-MINIFS($A$2:$A$100,$E$2:$E$100,E2) and press Enter.
- 4. Apply elapsed time formatting: Select the duration cells, press Ctrl+1, choose Custom, and enter [h]:mm:ss to display hours, minutes, and seconds, including values over 24 hours.
- 5. Save and share in Excel format: Click File, save the workbook as .xlsx so it remains fully compatible with Microsoft Excel and other spreadsheet apps.
Frequently Asked Questions
Why does my duration formula show a decimal instead of a time?
The cell is formatted as General or Number. Select the cell, press Ctrl+1, choose Custom, and enter [h]:mm:ss to display it as elapsed time.
Can I use MINIFS and MAXIFS in Microsoft Excel as well as WPS Spreadsheets?
Yes. Both applications support MINIFS and MAXIFS, and the .xlsx workbook can be opened and recalculated in either program.
How do I handle session durations that are longer than 24 hours?
Use the custom format [h]:mm:ss. The square brackets keep hours accumulating beyond 24 instead of resetting after one day.
How can I assign session numbers automatically when the start-time gap is one hour or more?
Add a helper column and use an IF formula based on the previous start time, such as =IF(A2-A1>=TIME(1,0,0),N(E1)+1,N(E1)), then fill down. Adjust the cell references to match your data.
Do I need to sort the data before assigning session numbers?
Yes. Sort the rows by start time in ascending order first. If the data is unsorted, the one-hour gap rule may put rows into the wrong session.
