Function Problems

How to Apply Excel Conditional Formatting Based on a Date in Another Column

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to format specific cells in one column when the corresponding date in another column is earlier than today.

Product: Excel
Device & OS: not provided
Scenario: Dynamically highlighting or styling cells based on past dates located in a related column using a custom formula.
Observed behavior: A formula-based conditional formatting rule needs to be applied to a specific range to achieve the targeted cell styling.

Before you start

Ensure that your date column is properly formatted as dates, and correctly identify both the target range you want to format and the reference column containing the dates.

Solution 1Recommended

Use a Formula-Based Conditional Formatting Rule

Apply a custom formula using the TODAY() function to conditionally format cells based on past dates in a different column.

To format cells based on another column's date, you must use the 'Use a formula to determine which cells to format' option. This allows you to reference external columns and dynamically compare their values to the current date using the TODAY() function.

Select the Target Range

Highlight the range of cells you want to apply the formatting to, for example, F2:F100.

Open Conditional Formatting

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

Choose Formula Option

In the New Formatting Rule dialog box, click on 'Use a formula to determine which cells to format'.

Enter the Formula

In the formula box, enter =AND($I2<TODAY(),$I2<>""). Make sure to adjust the row number in the formula ($I2) so it perfectly matches the first row of your selected range (F2).

Apply Formatting

Click the 'Format' button, choose your desired fill color, font style, or borders, and click 'OK' to save the rule.

Formula Explanation: The formula checks two conditions: whether the date in column I is earlier than today, and whether the cell in column I is not blank. The absolute column reference ($I) is crucial because it locks the check to column I, while the relative row number allows the rule to apply down the entire selected range.

Efficient Spreadsheet Management

Apply Conditional Formatting Easily with WPS Spreadsheet

WPS Office provides full support for advanced conditional formatting rules, including custom formulas and dynamic date references. You can easily highlight important data across your worksheets with a familiar interface.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing the data you want to format.
2. Highlight the target cells: Select the range of cells in the column you wish to apply the visual formatting to.
3. Access Conditional Formatting: Go to the 'Home' tab, click 'Conditional Formatting', and choose 'New Rule'.
4. Input the date formula: Select the formula option, type your date-referencing formula (e.g., referencing the TODAY() function), set your formatting style, and click 'OK'.

Fully compatible with Microsoft Excel conditional formatting rules and formulas.Lightweight and runs smoothly on Windows, Mac, and Linux.Intuitive interface for managing multiple conditional formatting rules seamlessly.Free to use with comprehensive and powerful spreadsheet capabilities.

Frequently Asked Questions

Why is my conditional formatting applying to the wrong rows?

This usually happens if the row number in your formula does not match the first row of your selected range. For example, if your selected range starts at F2, your formula must reference row 2 (e.g., $I2). If it references $I1 instead, the formatting will be offset by one row.

Can I format the entire row based on the date column?

Yes, you can format the whole row. Instead of selecting just one column (like F2:F100), select your entire data table (e.g., A2:Z100) before creating the new rule. Keep the same formula with the absolute column reference ($I2), and the entire row will highlight when the condition is met.

How do I change the formula to highlight future dates instead?

To highlight future dates, change the less-than operator (<) to a greater-than operator (>) in your formula. The formula would then become =AND($I2>TODAY(),$I2<>"").

Excel & Data Problems