Function Problems

How to Highlight Zero Quantity and Future Dates in Excel via Conditional Formatting

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to automatically highlight specific rows in a spreadsheet when two conditions are met simultaneously: the billed quantity is exactly zero, and the start date is after the current date.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking billing records where future scheduled items that show a zero billed quantity require visual attention on the worksheet.
Observed behavior: The user wants cells or rows matching both the zero quantity and future date criteria to be dynamically highlighted using conditional formatting rules.

Before you start

Ensure that your 'Start Date' column contains valid dates recognized by the spreadsheet program, rather than plain text, so the TODAY() function can evaluate them correctly.

Solution 1Recommended

Apply a Formula-Based Conditional Formatting Rule

Combine the AND and TODAY functions within a custom conditional formatting rule to evaluate multiple criteria at once and apply a highlight color.

By utilizing the AND function, you can ensure that the formatting is only triggered when both the zero quantity condition and the future date condition are met simultaneously.

Select the target data range

Highlight the entire data range you wish to format. Take note of the active cell (e.g., A2) which is usually the top-left cell of your selection, as your formula must reference it relative to this cell.

Open Conditional Formatting settings

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

Enter the logical formula

Choose the option 'Use a formula to determine which cells to format'. In the formula bar, input =AND($B2=0,$A2>TODAY()). Ensure that column B corresponds to your Quantity and column A corresponds to your Start Date. The dollar signs lock the column references.

Set the highlight format

Click the 'Format' button, navigate to the 'Fill' tab, and choose your preferred highlight color. Click 'OK' twice to apply the rule to your selected range.

Dynamic Updates: Because this rule uses the TODAY() function, the formatting will automatically recalculate and update itself every time you open or refresh the workbook.

Solution 2

Exclude Blank Cells from the Formatting Rule

Prevent the rule from accidentally highlighting empty date cells by adding an extra condition to ignore blanks.

Efficient Spreadsheet Formatting

Apply Conditional Formatting Easily with WPS Spreadsheet

WPS Spreadsheet features robust conditional formatting tools that are perfectly compatible with standard spreadsheet formulas. You can set up advanced, multi-condition rules quickly to automate your data highlighting.

1. Highlight your data: Open your file in WPS Spreadsheet and select the rows or columns you want to format.
2. Access the Conditional Formatting tool: Go to the 'Home' tab and click on the 'Conditional Formatting' icon, then select 'New Rule'.
3. Input your formula: Select 'Use a formula to determine which cells to format', enter your AND/TODAY formula, select a Fill color, and click OK to apply.

Fully compatible with Microsoft Excel formatting and advanced logical formulas.Lightweight, fast, and highly responsive when handling large datasets.Free to use with an intuitive interface for quick data visualization.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting applying to the wrong rows?

This usually happens if the cell references in your formula do not align with the active cell in your selected range. For example, if you highlight from row 2 but your formula references row 1 (e.g., $B1=0), the formatting will be offset by one row. Always match the formula row number to your selection's starting row.

How can I add a different highlight color for other zero values?

You can add a second, separate conditional formatting rule. Select your range again, create a new rule with the formula =$B2=0, set a different color (like green), and apply it. You can manage rule priority in the 'Manage Rules' menu if conditions overlap.

Why does the TODAY() function not highlight today's date?

The >TODAY() criteria strictly looks for dates that are greater than (after) the current date. If you also want to highlight items starting exactly on today's date, you should change the operator to greater than or equal to: >=TODAY().

Excel & Data Problems