Extract Dates from an Excel Rota by Person and Shift Value in WPS Office
Question details
The user needs to extract all dates from a staff rota where a selected employee is assigned a selected shift, using Excel formulas in WPS Office.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A rota has employee names in the first column, dates in the header row, and shift codes in the body. The user wants to return matching date values for a given person and shift.
- Observed behavior
- The goal is to produce a dynamic list of matching dates without manually scanning the rota.
Confirm that employee names are in one column, such as A2:A20, dates are in the header row, such as B1:Z1, and shift values are in the body range, such as B2:Z20. Adjust the formula ranges to match your actual rota size before pressing Enter.
Use FILTER and TOCOL to return matching rota dates
Use this dynamic-array formula when your rota stores names in the first column, dates in the header row, and shifts in the body range, and you need a spill list of matching dates.
The formula combines FILTER to select header cells where both the employee row and shift value match, then uses TOCOL with the ignore parameter 1 to stack the matching dates into a single column.
Launch WPS Office, click File > Open, and select the workbook that contains the rota.
Select cell H2 and type the employee name. Select cell H3 and type the shift value exactly as it appears in the rota, such as Early or Night.
Select an empty result cell and enter =TOCOL(FILTER(B$1:Z$1,(A$2:A$20=H2)*(B$2:Z$20=H3),"No matches"),1). Press Enter.
If data extends beyond row 20 or column Z, update B$1:Z$1, A$2:A$20, and B$2:Z$20 in the formula to cover your full range.
Extract Rota Dates by Employee and Shift with WPS Spreadsheets
WPS Spreadsheets can directly perform this rota lookup with Excel-compatible FILTER and TOCOL dynamic-array formulas. Enter the formula in an empty cell, and WPS Office returns all matching dates automatically.
- 1. Download and install WPS Office: Go to the WPS Office download page, install the free suite, and open WPS Spreadsheets.
- 2. Open your Excel rota: Click File > Open in WPS Spreadsheets and select the .xlsx rota file.
- 3. Enter the person and shift criteria: Use H2 for the employee name and H3 for the shift value exactly as it appears in the rota.
- 4. Paste the formula and refresh: In an empty cell enter =TOCOL(FILTER(B$1:Z$1,(A$2:A$20=H2)*(B$2:Z$20=H3),"No matches"),1) and press Enter to spill the dates.
- 5. Adjust ranges and formatting: Update the range references to the full rota size and apply Date formatting to the result cells if the dates display as numbers.
Frequently Asked Questions
Why does the formula return a #SPILL! error?
A #SPILL! error means something is already in the cells where the formula wants to spill. Clear the cells below the formula or move the formula to an empty area with enough blank rows and columns.
Can I change the "No matches" fallback to a blank cell?
Yes, replace "No matches" with "" in the IF-empty argument, and the formula will return an empty string when no dates match.
Do FILTER and TOCOL work in older WPS Office versions?
FILTER and TOCOL require a WPS Office version that supports dynamic arrays. If you receive a #NAME? error, update WPS Office to a supported version.
What if my dates display as serial numbers after using the formula?
Select the result range, press Ctrl+1, choose Date, and pick a date format. WPS stores dates as numbers, so formatting the cells displays them as dates.
