Formula Errors

How to Use AND and TODAY in Excel Conditional Formatting Rules

John Wilson Sep 30, 2026 868 views

Question details

The user needs to highlight future Finish Dates only when the project Status is marked as Completed using a conditional formatting formula.

How to Use AND and TODAY in Excel Conditional Formatting Rules

Product: Excel
Device & OS: not provided
Scenario: Applying conditional formatting across entire rows based on multiple criteria involving text values and future date comparisons.
Observed behavior: The conditional formatting rule initially failed to apply because plain text column names were used instead of valid cell references or structured table references.

Before you start

Ensure your dataset is organized in a clear tabular format. Note the exact column letters containing your Status and Date data before writing the formula.

Solution 1Recommended

Use Standard Cell References for Regular Data Ranges

Apply conditional formatting using standard absolute column and relative row references across your dataset.

For standard worksheet ranges, you must use valid cell references rather than plain text column names. By using a dollar sign ($) before the column letter, you lock the column reference. This ensures that every cell in the row checks the specific Status and Date columns, allowing the entire row to be highlighted correctly.

Select your data range

Highlight the entire dataset where you want the conditional formatting applied, for example, A2:F100. Make sure not to include the header row in your selection.

Open Conditional Formatting menu

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

Enter the logical formula

Choose 'Use a formula to determine which cells to format'. Enter the formula: =AND($A2="COMPLETED",$B2>TODAY()), assuming the Status is in column A and the Finish Date is in column B.

Set the format style

Click the Format button, select your desired Fill color to highlight the rows, and click OK twice to apply the formatting rule.

Use Standard Cell References for Regular Data Ranges

Match Row Numbers: Make sure the row number in your formula (e.g., $A2) exactly matches the first row of your selected data range.

Solution 2

Use Structured References for Excel Tables

Apply specific structured formulas if your data is officially formatted as an Excel Table.

Easily Apply Complex Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet offers robust, seamless support for conditional formatting, including advanced formulas with AND, TODAY, and other logical functions. It provides a highly compatible and intuitive interface for complex data analysis tasks.

1. Open your dataset: Launch WPS Spreadsheet and open your spreadsheet file containing the status and dates.
2. Select the target range: Highlight the entire data range you wish to format, making sure to start from the first row of actual data.
3. Add the formatting rule: Navigate to the Home tab, click Conditional Formatting, and select New Rule. Choose 'Use a formula to determine which cells to format'.
4. Input formula and apply: Enter your AND/TODAY formula (e.g., =AND($A2="COMPLETED",$B2>TODAY())), set your desired highlighting color under Format, and click OK.

Fully compatible with Microsoft Excel formulas and .xlsx filesIntuitive Conditional Formatting manager to easily edit and view rulesLightweight software providing fast performance even with large datasetsFree and comprehensive office suite alternative

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows or staggering the highlight?

This typically occurs if you omit the dollar sign ($) before the column letter in your formula. Using an absolute column reference (like $A2 instead of A2) ensures that every cell in the row evaluates based on the correct target columns.

Can I use my exact text headers directly in my conditional formatting formula?

No, conditional formatting requires valid cell references (e.g., $A2) or proper structured references if using an Excel Table. You cannot simply type plain text column names into the rule manager.

Why does my structured formula work in Microsoft 365 web but not in the desktop application?

Some desktop versions of Excel have stricter syntax requirements or known bugs regarding structured table references inside conditional formatting rules. If a structured reference fails, reverting to standard absolute cell references (e.g., $A2) is the most reliable workaround.

Excel & Data Problems