Formatting Issues

Highlight Excel Cells When Two Date Columns are Overdue

Adam DavisAdam Davis Nov 10, 2026 870 views

Question details

The user needs to highlight cells in a specific column when the dates in either of two other columns have passed the current date.

Highlight Excel Cells When Either of Two Date Columns Is Overdue
Product
Excel
Device & OS
not provided
Scenario
Tracking project deadlines or overdue tasks across multiple date columns.
Observed behavior
The user wants a conditional formatting rule that evaluates actual date values instead of relying on whether another cell is visually colored.
Before you start

Ensure your date columns are formatted as actual dates rather than text strings so the TODAY() function can accurately compare the values.

Solution 1Recommended

Use OR and AND Functions in Conditional Formatting

Create a custom formula rule to evaluate if either of the two date cells is not blank and is less than today's date.

To evaluate multiple conditions across different columns, you can combine the OR and AND logical functions within a single conditional formatting rule. This ensures that the cell is only highlighted if a valid date exists and that date is strictly in the past.

1
Select the Target Range

Highlight the cells in your target column (e.g., Column X, starting from X2) where you want the formatting to appear.

2
Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting', and choose 'New Rule' from the dropdown menu.

3
Enter the Custom Formula

Select 'Use a formula to determine which cells to format'. In the formula box, enter: =OR(AND(V2<>"",V2<TODAY()),AND(W2<>"",W2<TODAY())). Adjust V2 and W2 to match the first row of your actual date columns.

4
Apply Formatting

Click the 'Format' button, switch to the 'Fill' tab, choose a highlight color (like red or yellow), and click 'OK' twice to apply the new rule.

Use OR and AND Functions in Conditional Formatting
Approaching Expiration: You can modify this formula to alert you of upcoming deadlines by adding a buffer. For example, using V2<TODAY()+7 will highlight dates that are due within the next 7 days.
Seamless Spreadsheet Data Management

Manage Overdue Dates Easily with WPS Spreadsheet

WPS Spreadsheet provides robust conditional formatting tools that are completely compatible with Excel formulas, allowing you to highlight overdue dates, manage project deadlines, and streamline your workflow effortlessly.

  1. 1. Open your worksheet: Launch WPS Spreadsheet and open the file containing your date columns.
  2. 2. Select the column: Highlight the column where you want the conditional formatting to appear (e.g., Column X).
  3. 3. Apply Conditional Formatting: Go to the Home tab on the top menu, click 'Conditional Formatting', and select 'New Rule'.
  4. 4. Input the formula: Choose 'Use a formula to determine which cells to format', input =OR(AND(V2<>"",V2<TODAY()),AND(W2<>"",W2<TODAY())), configure your highlight color, and click OK.
Fully supports advanced conditional formatting and complex logical functions like OR and AND.100% compatible with Microsoft Excel (.xlsx, .xls) file formats.Free to download with a lightweight, intuitive interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why is conditional formatting highlighting my blank cells as overdue?

Excel interprets completely blank cells as a zero value, which corresponds to January 0, 1900. Because this date is far in the past, it evaluates as less than TODAY(). Adding the condition V2<>"" inside your AND function ensures blank cells are ignored.

Can I apply this conditional formatting rule to highlight the entire row instead of just one column?

Yes. Simply select your entire data range (e.g., A2:Z100) instead of just Column X. When entering your formula, lock the column references with a dollar sign: =OR(AND($V2<>"",$V2<TODAY()),AND($W2<>"",$W2<TODAY())). This applies the check across the whole row.

How do I change the color of the highlighted cells later?

Navigate to Home > Conditional Formatting > Manage Rules. Select your existing rule from the list, click 'Edit Rule', and then click the 'Format' button to choose a new fill color or font style.