Use Excel Icon Sets Based on Dates in Two Columns in WPS Office
Question details
User wants to create task-status indicators in WPS Spreadsheets using icon sets or conditional formatting based on whether two date columns are blank or populated.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is tracking tasks and needs visual status markers for not started, started, and complete tasks based on start date and end date cells.
- Observed behavior
- The desired result is conditional formatting that automatically displays the correct icon or color when the start date and end date conditions are met.
Open your WPS Spreadsheets workbook and identify the start date and end date columns, then note the first row of your task range. Formula-based conditional formatting uses that first-row cell reference as the relative anchor for the entire range.
Create formula-based conditional formatting rules for each task status
Use this method when you need WPS Spreadsheets to automatically show not started, started, or complete indicators based on the presence of dates in start and end date columns.
Formula-based conditional formatting lets you evaluate two date cells per row and return TRUE only when the correct combination of blank and populated cells is met.
Adjust the cell references in the formulas to match the first row of your selected range; WPS Spreadsheets applies the rule relatively to the rest of the range.
Open your WPS Spreadsheets workbook, click the first cell in the task range, then drag to select all rows and columns where the status indicators should appear.
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(ISNUMBER(C2),NOT(ISNUMBER(D2))) and then click Format to assign the icon set or fill color for started tasks. Replace C2 and D2 with your start date and end date cells for the first row.
Repeat New Rule and enter =AND(NOT(ISNUMBER(C2)),NOT(ISNUMBER(D2))) for not started and =AND(ISNUMBER(C2),ISNUMBER(D2)) for complete, assigning a different visual format to each rule.
Use a helper status column with WPS built-in Icon Sets
Use this approach if you want to apply the actual Icon Sets feature to a single status column rather than coloring an entire row or cell with formula rules.
Create Date-Based Task Status Indicators in WPS Spreadsheets
WPS Spreadsheets lets you use conditional formatting formulas to automatically mark tasks as not started, started, or complete based on start and end date cells. Build the rule once and it applies across your tracking sheet without changing your existing Excel file.
- 1. Download and install WPS Office: Download WPS Office from the official WPS site, run the installer, and open the WPS Spreadsheets app.
- 2. Open your workbook: Open the Excel file that contains your task list and date columns, or create a new blank spreadsheet.
- 3. Apply the conditional formatting rules: Select your task range, go to Home > Conditional Formatting > New Rule, and create the three formula-based rules for not started, started, and complete statuses.
- 4. Test with sample dates: Enter a few dates in the start and end date columns to verify that the indicators update automatically for each row.
Frequently Asked Questions
Can I use date-based conditional formatting with WPS Office for free?
Yes, WPS Spreadsheets includes conditional formatting. Go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and create the date-based rules. It works with .xlsx files.
Why is my conditional formatting rule not matching any rows?
Check that the formula references the first row of the applied range and that the date cells contain real date values or numbers. Text values that look like dates will not return TRUE with ISNUMBER.
Can I show three different icons instead of cell colors?
Yes, create a helper status column that returns 1, 2, or 3 for each task state, then select the column and apply Home > Conditional Formatting > Icon Sets. Map the three values to the desired symbols in Edit Rule.
How do I edit or remove a date-based conditional formatting rule in WPS Spreadsheets?
Select the range, go to Home > Conditional Formatting > Manage Rules, choose the rule, and click Edit Rule to change it or Delete Rule to remove it.
Does this method work in Microsoft Excel too?
Yes, the same formula logic works in Microsoft Excel and WPS Spreadsheets because both support formula-based conditional formatting and .xlsx file compatibility.
