How to Highlight Dates Older Than 60 Days with a Blank Cell in WPS Spreadsheets
Question details
The user needs to create a conditional formatting rule in WPS Spreadsheets that highlights dates in column F when the corresponding cell in column I is blank and the date is more than 60 days old.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is managing a sheet where a date in column F should only be highlighted if the paired cell in column I is empty and the date is more than 60 days in the past.
- Observed behavior
- The current sheet does not have a rule that applies this specific combination of blank cell and date condition.
Open the workbook in WPS Spreadsheets and confirm that the dates in column F are stored as real date values, not text, and that the paired cells in column I are genuinely empty rather than containing spaces or formulas that return empty text.
Create a Formula-Based Conditional Formatting Rule in WPS Spreadsheets
Use this method when you need a single rule that combines a blank-cell check with a date-older-than-60-days check.
The formula =AND($I1="",$F1<TODAY()-60) tests both conditions on each row. The dollar signs lock the columns while allowing the row number to change, so make sure the row number matches the first row of your selected range.
Select the first cell in column F, such as F1, then drag down or press Ctrl+Shift+Down arrow to select the full range you want to format.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
In the formula box, enter =AND($I1="",$F1<TODAY()-60). Change 1 to the row number of the first selected cell if your range starts on a different row.
Click Format, choose the desired highlight, such as a fill color or bold font, and click OK.
Click OK in the New Formatting Rule dialog, then check that only dates older than 60 days with a blank column I cell are highlighted.
Apply Conditional Formatting by Formula in WPS Spreadsheets
Create a formula-based rule in WPS Spreadsheets to highlight column F dates when column I is blank and the date is older than 60 days. The same conditional formatting logic works with Microsoft Excel .xlsx files, so you can open, edit, and share workbooks without compatibility issues.
- 1. Select the target range: Select the cells in column F, starting from the first row you want to format.
- 2. Open the formula rule dialog: Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- 3. Enter the conditional formula: Type =AND($I1="",$F1<TODAY()-60) and adjust the row number to match the first selected row.
- 4. Choose a format: Click Format, select a fill color or font style, and click OK.
- 5. Confirm the rule: Click OK to apply the rule and verify the highlighted rows meet both conditions.
Frequently Asked Questions
Can I use this rule if my data starts in row 2 instead of row 1?
Yes. Select the range starting at F2 and use the formula =AND($I2="",$F2<TODAY()-60) so the row reference matches the first selected row.
Why is my date not highlighted even though it is older than 60 days?
Check three things: the date in column F is a real date value, the corresponding cell in column I is actually blank, and the row number in the formula matches the first row of the selected range.
Will the highlight update automatically when today's date changes?
Yes. The TODAY() function is volatile, so WPS Spreadsheets recalculates the conditional formatting rule when the workbook opens or when relevant changes occur.
Does this conditional formatting work with Excel files?
Yes. Formula-based conditional formatting is compatible with Microsoft Excel .xlsx files, so the rule works in both WPS Spreadsheets and Excel.
