Function Problems

How to Highlight Excel Cells When Quantity is Zero and Date is in the Future

Rana Garcia Sep 28, 2026 869 views

Question details

The user needs to apply formatting rules to highlight specific cells based on three simultaneous conditions: a quantity billed equal to zero, a start date greater than today's date, and ensuring blank date cells are ignored.

Highlight Cells When Quantity Is Zero and Date Is in the Future

Product: Spreadsheets
Device & OS: not provided
Scenario: Tracking billing records, project schedules, or inventory statuses where missing quantities on upcoming scheduled dates require immediate visual flagging.
Observed behavior: The user requires target cells in specific columns to automatically change background color when the date and quantity criteria are met, while explicitly preventing empty start date cells from triggering the highlight.

Before you start

Verify that your Start Date column contains actual date values rather than text strings, and confirm the exact column letters for both your Date and Quantity data before writing the custom formula.

Solution 1Recommended

Use Conditional Formatting with the AND Function

Create a custom conditional formatting rule utilizing the AND formula to evaluate multiple criteria (zero quantity, future date, and non-blank cells) simultaneously.

By utilizing the AND function within the Conditional Formatting rules manager, you can enforce multiple conditions that must all be true for the formatting to apply. Locking the column references with a dollar sign ensures the highlight spans across your selected row correctly.

Select the target data range

Click and drag to select the data range you want to format, such as A2:B100. Ensure that the top-left cell of your selection (e.g., A2) is the active cell, as the formula will be based on this starting point.

Open the Conditional Formatting menu

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

Choose the formula option

In the New Formatting Rule dialog box, select the option labeled 'Use a formula to determine which cells to format'.

Enter the custom AND formula

In the formula input box, type exactly: =AND($B2=0,$A2>TODAY(),$A2<>""). This assumes Column A holds the Start Date and Column B holds the Quantity Billed.

Define the visual format and apply

Click the 'Format' button, switch to the 'Fill' tab, and choose your preferred highlight color. Click 'OK' to close the Format dialog, then click 'OK' again to apply the new rule to your selected range.

Use Conditional Formatting with the AND Function

Formula Breakdown: The $ locks the column references so the entire row highlights. The TODAY() function automatically updates to the current date, and $A2<>"" explicitly prevents Excel from treating blank cells as zeroes or valid dates.

Easily Format Spreadsheets with WPS Office

Apply Advanced Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet offers a robust, highly intuitive Conditional Formatting manager that handles complex custom formulas with ease. You can dynamically highlight critical data combinations like future dates and zero quantities using exact Excel syntax.

1. Select the data range: Open your workbook in WPS Spreadsheet and highlight the data range (e.g., A2:B100).
2. Navigate to Conditional Formatting: Go to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'.
3. Enter the rule: Choose 'Use a formula to determine which cells to format', input the formula =AND($B2=0,$A2>TODAY(),$A2<>""), and set your fill color.
4. Save and Apply: Click 'OK' to instantly view your intelligently highlighted data.

100% compatible with Microsoft Excel file formats (.xlsx, .xls) and custom formula syntax.Clean, intuitive interface for easily managing and prioritizing multiple conditional formatting rules.Free, lightweight, and fast-loading spreadsheet application.

microsoft office alternative - wps office

Frequently Asked Questions

Why are blank date cells being highlighted incorrectly?

Spreadsheet software sometimes interprets blank cells as a zero value, which in date format translates to January 0, 1900. Including the condition $A2<>"" in your AND formula explicitly tells the rule to ignore empty cells.

How do I make the highlight apply to the entire row instead of just one cell?

To highlight the entire row, you must select the full width of your data range before creating the rule, and you must use absolute column references by placing a dollar sign ($) before the column letters in your formula, such as $A2 and $B2.

Can I add a third condition, like checking for a specific status?

Yes, the AND function can evaluate multiple conditions. Simply add a comma and your new criteria inside the parentheses. For example: =AND($B2=0, $A2>TODAY(), $A2<>"", $C2="Pending").

Why is my conditional formatting highlighting the wrong rows entirely?

This offset usually happens if the active cell during your range selection doesn't match the row number written in your formula. If you select A2:B100 (where A2 is active), your formula must start referencing row 2.

Excel & Data Problems