How to Use Excel Conditional Formatting Based on Dates and Blank Cells in WPS Office
Question details
The user needs to apply Excel conditional formatting in WPS Office Spreadsheets to highlight a date-driven range based on whether column C is more than 10 days away, less than 4 days away, or column H contains a date, and to highlight column C when C is blank and H has a date.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is managing a date-based schedule or task list and wants at-a-glance visual highlights for the range A3:G100 and C3:C100 according to dates in columns C and H.
- Observed behavior
- The desired result is five fill rules: red when C3 has a date, amber when C3 is 4 or more days from today, yellow when C3 is 10 or more days from today, green when H3 has a date, and pink when C3 is blank and H3 has a date.
Make sure the date cells in columns C and H are formatted as real dates, not text, and keep A3 as the active cell when selecting A3:G100 so the formulas reference the correct starting row.
Apply Formula-Based Conditional Formatting for Date and Blank-Cell Rules
Use this solution to highlight the selected range with red, amber, yellow, green, and pink fills based on dates in columns C and H.
The formulas use $C3 and $H3 so the formatting follows the active row of the selected range. WPS Office Spreadsheets recalculates TODAY() each time the file opens, so the date-based colors update automatically.
Select A3:G100, or your target range, with A3 as the active cell. Go to the Home tab, click Conditional Formatting > New Rule, and select "Use a formula to determine which cells to format".
Enter each formula in separate rules and set the matching fill color: red =$C3<>"", amber =$C3>=TODAY()+4, yellow =$C3>=TODAY()+10, green =$H3<>"".
Select C3:C100, open Home > Conditional Formatting > New Rule, enter =AND($C3="",$H3<>""), choose pink fill, and confirm.
Open Home > Conditional Formatting > Manage Rules, select the green rule, and move it above the red, amber, and yellow rules so H3 dates take precedence.
Highlight Date and Blank-Cell Conditions Directly in WPS Office Spreadsheets
Open WPS Office Spreadsheets and use formula-based conditional formatting to apply red, amber, yellow, green, and pink fills. The same rules work in WPS Office, with a familiar Microsoft Excel-like ribbon and full .xlsx compatibility.
- 1. Open WPS Office Spreadsheets: Open WPS Office Spreadsheets and select the range A3:G100 with A3 as the active cell.
- 2. Create the first rule: Go to Home > Conditional Formatting > New Rule, choose "Use a formula to determine which cells to format", enter =$C3<>"", and set red fill.
- 3. Add the date and H-column rules: Repeat the New Rule flow to add amber =$C3>=TODAY()+4, yellow =$C3>=TODAY()+10, and green =$H3<>"" with their matching fills.
- 4. Add the pink rule and set priority: Select C3:C100, add the pink rule =AND($C3="",$H3<>""), then use Home > Conditional Formatting > Manage Rules to place the green rule above the others.
Frequently Asked Questions
Why does my conditional formatting rule not highlight the expected rows?
Check that the active cell is A3 before entering the formulas, make sure the dates in columns C and H are real dates, and review the rule order in Home > Conditional Formatting > Manage Rules.
Can I apply these rules to a different range?
Yes. Select the range you need and keep the top-left cell active, then adjust the formulas to match your date columns. Columns C and H are the example references.
How do I make the green rule override the red, amber, and yellow rules?
Open Home > Conditional Formatting > Manage Rules, select the green rule, and move it above the other rules. You can also enable Stop If True for the green rule if you want it to prevent lower-priority rules.
Can I use these formulas in Microsoft Excel files opened in WPS Office?
Yes. WPS Office Spreadsheets supports .xlsx and .xls files, and formulas such as TODAY(), AND(), and formula-based conditional formatting are compatible.
What does =$C3>=TODAY()+4 mean?
It checks whether the date in cell C3 is 4 or more days after today. Because TODAY() updates dynamically, the highlight changes automatically each day.
