Count Time Pairs at Least Two Hours Apart in WPS Spreadsheets
Question details
Count rows where both time cells are populated and the second time is at least two hours later than the first.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Start times are listed in A1:A100 and end times are listed in B1:B100. The goal is to count qualifying time pairs rather than add their durations.
- Observed behavior
- Only rows with nonblank time cells and a difference of at least two hours should be included in the result.
Confirm that columns A and B contain valid spreadsheet time values rather than text. If a time range can cross midnight, decide whether the end time should be treated as the next day before applying the formula.
Use SUMPRODUCT to count qualifying time pairs
Use this formula in WPS Spreadsheets or compatible Excel versions when you need to count nonblank rows whose end time is at least two hours after the start time.
SUMPRODUCT evaluates each row against three conditions: the start time is populated, the end time is populated, and the difference between the end and start times is at least two hours. Each qualifying row contributes 1 to the total.
Click an empty cell where you want the count to appear, such as D1.
Type =SUMPRODUCT(--(A1:A100<>""),--(B1:B100<>""),--(B1:A100-A1:A100>=TIME(2,0,0))) and press Enter.
Use =SUMPRODUCT(--(A1:A100<>""),--(B1:B100<>""),--(B1:B100-A1:A100>=TIME(2,0,0))) if the formula contains a typo in the third condition; the difference must be B1:B100 minus A1:A100.
Review the returned number. A row such as 12:00 to 13:00 is excluded, while a row such as 15:00 to 17:00 is counted.
Use the dynamic-array SUM formula in newer spreadsheet versions
Use this option in versions that support array calculations when you want a compact formula for counting qualifying rows.
Calculate the total qualifying hours instead of the row count
Use this approach when the desired result is the combined duration of qualifying time pairs rather than the number of qualifying rows.
Count Two-Hour Time Differences Easily in WPS Spreadsheets
WPS Spreadsheets lets you count populated start-and-end time pairs with a SUMPRODUCT formula, compare durations accurately, and format the result as either a row count or total hours.
- 1. Open your spreadsheet: Launch WPS Office, choose Spreadsheets, and open the workbook containing the start times in column A and end times in column B.
- 2. Choose the result cell: Click an empty cell where the qualifying row count should appear.
- 3. Enter the formula: Type =SUMPRODUCT(--(A1:A100<>""),--(B1:B100<>""),--(B1:B100-A1:A100>=TIME(2,0,0))) and press Enter.
- 4. Review or format the result: Confirm the count, or use the total-duration SUMPRODUCT formula and select Home > Number Format to display the result as [h]:mm.
Frequently Asked Questions
How do I count time pairs that are exactly two hours apart?
Use the same SUMPRODUCT formula with >=TIME(2,0,0). Because the condition includes greater than or equal to, pairs exactly two hours apart are counted.
Why does the formula require nonblank checks for both columns?
The conditions --(A1:A100<>"") and --(B1:B100<>"") prevent empty cells from being treated as valid time pairs and ensure only populated rows are counted.
What is the difference between counting pairs and summing hours?
The counting formula returns the number of qualifying rows. To total their durations, use =SUMPRODUCT(--(A1:A100<>""),--(B1:B100<>""),--(B1:B100-A1:A100>=TIME(2,0,0)),B1:B100-A1:A100).
How should I handle a time range that crosses midnight?
A simple subtraction may produce a negative result when the end time is after midnight. Use a date-and-time value in each cell, or adjust the end time with a next-day calculation before applying the two-hour condition.
