Check Multiple Time Ranges and Return a Team Value in WPS Spreadsheet
Question details
Create a formula that checks all records for the team in the current row and returns that team value when a timeline time falls within a matching start and end time range.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A timeline grid needs to identify whether the time in each column matches any record belonging to the team listed on the current row.
- Observed behavior
- The desired result is to display the current team value in matching timeline cells and leave nonmatching cells blank.
Make sure the start-time and end-time columns contain real date/time values rather than text. Confirm that the tolerance value in K1 uses the same time unit as the values in the timeline and record table.
Use COUNTIFS to return the team when a matching time range exists
Use this option when you only need to confirm that at least one record for the current team contains the timeline time. It is concise and avoids returning an error when no record matches.
This formula checks the team, start time, and end time together. It returns the value in column J only when at least one matching record is found.
Click cell L3, or select the first cell where the team result should appear for the time shown in L2.
Enter =IF(COUNTIFS(Table23[TEAM],$J3,Table23[STA],"<="&L$2+$K$1,Table23[STD],">="&L$2-$K$1)>0,$J3,"") and press Enter.
Use the fill handle at the lower-right corner of L3 to drag the formula across and down. The $J3 reference keeps the team column fixed, while L$2 keeps the timeline header row fixed.
Select the timeline result range, then choose Home > Conditional Formatting and create a rule for cells that are not blank or that equal the team number.
Use LET and FILTER for a dynamic-array matching formula
Use this option when you want a formula that explicitly filters the matching table records before deciding whether to return the team value.
Match Team Time Ranges with WPS Spreadsheet Functions
WPS Spreadsheet lets you build time-range checks with COUNTIFS, LET, and FILTER, then fill the formula through a timeline grid and highlight matching team values with conditional formatting.
- 1. Open the workbook in WPS Spreadsheet: Open the source .xlsx or .xls file and verify that the TEAM, STA, and STD columns are included in Table23.
- 2. Check the time inputs: Select the start, end, and timeline cells, then use Home > Number Format to confirm that they are recognized as date/time values.
- 3. Add the matching formula: Click the first result cell, such as L3, and enter the COUNTIFS formula that references the current team, timeline header, and tolerance cell.
- 4. Fill the timeline grid: Drag the fill handle across the timeline and down the team rows to apply the adjusted references.
- 5. Highlight returned values: Select the output range and choose Home > Conditional Formatting to format cells containing returned team values.
Frequently Asked Questions
Why does the formula return a blank when the timeline time looks like it is inside the range?
Check whether STA, STD, and the timeline header are stored as real date/time numbers. Select the cells and use Home > Number Format to apply a date or time format; text values cannot be reliably compared by COUNTIFS.
How do I match an exact time range without any tolerance?
Set K1 to 0. The formula then matches only when the timeline value in L2 is greater than or equal to STA and less than or equal to STD.
Can the formula return a team name instead of a team number?
Yes. Keep the team name in column J and in Table23[TEAM]. The formula returns $J3, so it can return text names as well as numeric identifiers.
What happens if more than one record matches the same team and time?
The COUNTIFS version still returns the team value because it only tests whether one or more matches exist. Use the LET and FILTER version if you need to inspect the individual matching records.
How can I color only the cells where a team was returned?
Select the output grid, choose Home > Conditional Formatting, and create a rule for nonblank cells or for cells equal to a specific team value.
