Use Conditional Formatting for Employee Start and End Dates in WPS Office Spreadsheets
Question details
The user needs to highlight calendar dates when they fall within the start and end dates associated with a selected employee ID.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is managing an employee calendar and wants to visually mark the period between each employee's start date and end date based on a selected employee ID.
- Observed behavior
- Calendar dates are not automatically highlighted when the employee ID or date range changes.
Prepare a worksheet that contains the calendar range, an employee ID input cell, and a lookup table with employee IDs, start dates, and end dates. Confirm that the calendar cells contain real dates rather than text values.
Use INDEX/MATCH in a Conditional Formatting Rule to Highlight Employee Date Ranges
Recommended when you need a dynamic calendar that updates from an employee ID and must work in Excel 2019 or WPS Office Spreadsheets without relying on newer functions.
Conditional formatting applies a fill when the rule formula returns TRUE. Because Excel and WPS store dates as numbers, the AND function can test whether a calendar date is greater than or equal to the start date and less than or equal to the end date.
INDEX/MATCH retrieves the correct start and end dates for the selected employee ID and is widely compatible with Excel 2019 and WPS Office Spreadsheets.
Select the calendar cells where the highlight should appear, for example A8:G12, making note of the active cell reference such as A8.
Go to the Home tab, click Conditional Formatting, and choose New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
In the formula box, enter: =AND(A8>=INDEX(start_dates,MATCH($B$3,employee_IDs,0)),A8<=INDEX(end_dates,MATCH($B$3,employee_IDs,0)))
Click Format, choose a fill color on the Fill tab, click OK, and confirm the rule applies to =$A$8:$G$12.
Use XLOOKUP Helper Cells for a Simpler Date Range Conditional Formatting Formula
Use this method when your version of WPS Office or Excel supports XLOOKUP and you prefer helper cells that are easier to audit.
Highlight Employee Start and End Date Ranges in WPS Office Spreadsheets
WPS Office Spreadsheets lets you use formula-based conditional formatting, just like Excel, to highlight calendar dates that fall between an employee’s start and end dates. Set up your employee ID lookup and dynamic date formatting in a familiar spreadsheet interface without changing your existing workflow.
- 1. Open the workbook in WPS Spreadsheets: Download and install WPS Office, open WPS Spreadsheets, then open the workbook that contains the employee calendar and date range data.
- 2. Prepare the employee date lookup: Ensure your workbook has columns for employee IDs, start dates, and end dates, and decide which cell will hold the selected employee ID, such as B3.
- 3. Select the calendar range and create a rule: Select the calendar dates to highlight, go to Home > Conditional Formatting > New Rule, and choose Use a formula to determine which cells to format.
- 4. Enter the conditional formatting formula: Enter a formula such as =AND(A8>=INDEX(start_dates,MATCH($B$3,employee_IDs,0)),A8<=INDEX(end_dates,MATCH($B$3,employee_IDs,0))) and set the desired fill color.
- 5. Verify with different employee IDs: Apply the rule, change the employee ID in B3, and confirm that the calendar highlights only the dates within that employee's start and end dates.
Frequently Asked Questions
Why are no dates highlighted after I apply the rule?
Check that the calendar cells contain real dates rather than text, that the employee ID in the lookup cell exactly matches the ID column, and that the formula uses the correct relative reference for the active cell in the selected calendar range.
Can I use this conditional formatting with an Excel Table?
Yes. If your version of WPS Office or Excel does not evaluate structured references directly in conditional formatting, use INDIRECT with the table name, or point the rule to helper cells or normal ranges to avoid compatibility issues.
Does this method work when I change the employee ID?
Yes. Because the formula references B3 or uses helper lookup cells, changing the employee ID recalculates the lookup and the conditional formatting automatically updates the highlight for the new employee date range.
Can I highlight multiple employees' date ranges on the same calendar?
Yes, but the formula must evaluate each calendar date against all employee rows. Use a COUNTIFS-based rule that counts records where the calendar date is greater than or equal to the start date and less than or equal to the end date, and format cells where the count is greater than zero.
Is XLOOKUP available in WPS Office Spreadsheets?
Current versions of WPS Office support XLOOKUP. If your version does not, use INDEX/MATCH instead, which produces the same employee start and end date lookup for conditional formatting.
