WPS Spreadsheets: Highlight Rows When a Date Is Over 14 Days Old
Question details
Create a conditional-formatting rule that highlights an entire row when the date in column E is more than 14 days old and the corresponding cell in column O is blank.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet tracks letter dates in column E and response dates in column O. The user needs overdue rows to stand out automatically.
- Observed behavior
- Rows are not automatically highlighted when the date in column E exceeds 14 days and column O has no response date.
Confirm that columns E and O contain valid dates or blank cells, and identify the first data row, such as row 2. Select the complete data range, including the columns and rows that should receive the formatting.
Highlight overdue rows when the response-date cell is blank
Use this formula-based conditional-formatting rule when only rows without a response date should be highlighted after 14 days.
The absolute column references keep the rule focused on columns E and O, while the relative row references allow WPS Spreadsheets to evaluate each row in the selected range. Replace row 2 with the first data row in your worksheet if necessary.
In WPS Spreadsheets, drag across the complete table range that should be formatted, beginning with the first data row, such as A2:O100.
Go to the Home tab, select Conditional Formatting, and choose the option to create a new rule or use a formula to determine which cells to format.
Enter =AND(ISBLANK($O2),TODAY()-$E2>14) in the formula field. Keep the column letters absolute and the row number relative to the first selected row.
Click Format or Format Style, choose a fill color or another highlight style, and select OK to save the rule.
Highlight both missing and late responses
Use this expanded formula when a row should be highlighted either because no response exists after 14 days or because the response arrived more than 14 days after the letter date.
Create automatic overdue-row highlights in WPS Spreadsheets
WPS Spreadsheets lets you apply formula-based conditional formatting to an entire data range, so overdue rows update automatically as TODAY() changes and response dates are entered.
- 1. Open the worksheet: Launch WPS Office, open your spreadsheet, and locate the table containing the letter dates and response dates.
- 2. Select the table range: Select the complete range to which the row highlight should apply, starting with the first data row.
- 3. Create a formula rule: Use Home > Conditional Formatting and create a formula-based rule with =AND(ISBLANK($O2),TODAY()-$E2>14).
- 4. Apply a highlight style: Choose a fill color or other format, then click OK or Apply to activate the rule.
Frequently Asked Questions
Why does the formula use $O2 and $E2?
The dollar signs lock the column references to E and O, while the row number remains relative. This lets WPS Spreadsheets evaluate the date and response cell for each row in the selected range.
What should I change if my first data row is row 3?
Use row 3 in the formula, such as =AND(ISBLANK($O3),TODAY()-$E3>14), and make sure the selected conditional-formatting range also begins on row 3.
How can I highlight responses that arrived late too?
Edit the rule through Home > Conditional Formatting and use =IF(ISBLANK($O2),TODAY()-$E2>14,$O2-$E2>14). This checks the current date when O is blank and compares the response date with the letter date when O contains a date.
Why are some rows not highlighted even though the dates look older than 14 days?
Check that the cells in column E contain valid spreadsheet dates rather than text, that the selected range includes those rows, and that the formula row matches the first row of the range.
