WPS Spreadsheets: Highlight Entire Rows for Overdue Dates and Times
Question details
Create conditional-formatting rules that highlight an entire worksheet row when its due date and time are overdue or will occur within the next 24 hours.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet tracks document due dates and times in a due-date column, and the full record row must be visually flagged based on the deadline.
- Observed behavior
- Rows with a due date and time earlier than the current time should display an overdue format, while rows due within 24 hours should use a separate format.
Make sure the due-date cells contain real date-and-time values rather than text. Select the full table range beginning with the first data row, and use that row number in each formula.
Apply a red fill to entire rows with overdue dates and times
Use this rule when a row should be highlighted after the date and time in its due-date column have passed.
This example assumes that the due date and time are in column B, headers are in row 1, and the data range extends from column A through column T. The dollar sign locks column B while allowing each row to be evaluated separately.
In WPS Spreadsheets, select the rows and columns to format, such as A2:T1000. Do not select only the due-date column if you want the entire row highlighted.
Go to Home > Conditional Formatting > New Rule, choose the option to use a formula for determining which cells to format, and enter =AND($B2<>"",$B2<NOW()).
Select Format, choose a red Fill color, and click OK. Confirm the rule applies to the complete selected range, such as $A$2:$T$1000.
Highlight rows due within the next 24 hours
Add this separate rule to identify upcoming deadlines that have not yet passed.
Set overdue and upcoming deadline rules in WPS Spreadsheets
WPS Spreadsheets lets you apply formula-based conditional formatting across complete table rows. Use NOW() with a fixed due-date column reference to automatically identify overdue records and deadlines that are approaching.
- 1. Open the worksheet in WPS Spreadsheets: Open your due-date tracking workbook and identify the column that stores each document deadline.
- 2. Select all columns in the record area: Drag to select the full table range, such as A2:T1000, so each rule formats complete rows.
- 3. Add the overdue formula rule: Select Home > Conditional Formatting > New Rule and use =AND($B2<>"",$B2<NOW()) with a red fill.
- 4. Add the 24-hour reminder rule: Create another formula rule with =AND($B2<>"",$B2>=NOW(),$B2<=NOW()+1), then assign a different fill color.
Frequently Asked Questions
Why does only the due-date cell change color instead of the full row?
The rule's Applies to range likely includes only the due-date column. Select the complete table range, such as A2:T1000, while keeping $B2 as the formula reference to the due-date cell.
How do I change the formula if my due dates are in column G?
Replace $B2 with $G2 in both formulas. For example, use =AND($G2<>"",$G2<NOW()) for overdue rows and apply the rule to the entire row range you want colored.
Why are some overdue deadlines not being highlighted?
Check that the due-date cells are actual date-and-time values, not text. Click a cell and review its format under Home; you may need to convert text dates before the NOW() comparison can work.
Can I highlight deadlines due in the next 48 hours instead of 24 hours?
Yes. In Home > Conditional Formatting > New Rule, change NOW()+1 to NOW()+2. The number after the plus sign represents the number of days in the upcoming deadline window.
