Formatting Issues

How to Use Excel Conditional Formatting for Date and Status

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to highlight a specific date cell when it is within three days of the current date and simultaneously apply color formatting to an adjacent status cell based on its value (Waiting, Received, or Not Received).

Product: Excel
Device & OS: not provided
Scenario: Tracking upcoming deadlines by color-coding dates and their corresponding progress statuses automatically using formulas.
Observed behavior: Requires a solution to evaluate both the date condition and the status text simultaneously without causing rule conflicts or offset issues.

Before you start

Ensure your date column contains valid Excel dates (not text strings) and verify that the status column values exactly match your target words without extra leading or trailing spaces.

Solution 1Recommended

Apply Conditional Formatting Using the AND Formula

Use a formula-based conditional formatting rule to evaluate both the date's proximity to today and the exact status text simultaneously.

To format cells based on multiple conditions (such as a date being close and a specific status being present), you must use a formula rule. The AND function allows you to ensure all conditions are met before applying the color.

Select the Target Range

Highlight the cells you want to format (e.g., H2:I100). Make sure your selection starts from row 2, as this will match the row referenced in your formula.

Create a New Rule

Navigate to the Home tab, click on 'Conditional Formatting' in the Styles group, and select 'New Rule'.

Enter the Formula

Choose 'Use a formula to determine which cells to format'. For the 'Not Received' status, enter: =AND(H2<>"",H2<=TODAY()+3,$I2="Not Received"). Adjust H and I to match your date and status columns.

Set the Format and Repeat

Click the 'Format' button, choose your desired fill color, and click 'OK'. Repeat this entire process, changing the text condition to "Waiting" and "Received" with their respective colors.

Avoid Offset Errors: Make sure your selected range starts at the exact same row referenced in your formula (e.g., Row 2). If the selection starts at Row 3 but the formula says H2, the formatting will be offset.

Solution 2

Resolve Conflicting Conditional Formatting Rules

Fix issues where specific-text rules override your complex date-based formula rules.

Efficient Data Management

Manage Dates and Statuses Easily with WPS Spreadsheet

WPS Spreadsheet provides robust, highly compatible conditional formatting tools to help you track deadlines and statuses effortlessly. It fully supports advanced formulas like AND() and TODAY() with a smooth, intuitive interface.

1. Open Your Workbook: Launch WPS Spreadsheet and select the range containing your dates and statuses.
2. Access Conditional Formatting: Go to the Home tab and click on 'Conditional Formatting', then select 'New Rule'.
3. Input the Formula: Choose 'Use a formula to format cells' and type your formula (e.g., =AND(H2<>"",H2<=TODAY()+3,$I2="Waiting")).
4. Apply Color: Click 'Format' to pick a highlight color, confirm, and watch your deadline statuses update automatically.

Fully compatible with Microsoft Excel conditional formatting rules and formulas.Easily apply complex, formula-based cell highlighting.Track deadlines seamlessly with robust built-in date functions.Free and lightweight alternative for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows?

This usually happens when the selected range is offset from the formula references. Ensure that the row number in your formula (e.g., H2) matches the starting row of the range you selected before applying the rule.

Can I use this formula for different status names?

Yes, simply replace the text "Not Received" in the formula with your exact status name, such as "Waiting" or "Completed", making sure to keep the quotation marks.

Why is the status color not changing even when the date is within 3 days?

Check for typos or extra spaces in your data cells. If your cell says "Waiting " (with a space) but the formula says "Waiting", it will not trigger. Also, ensure there are no conflicting text-based conditional formatting rules overriding your formula rule.

What does the H2<>"" part of the formula do?

The H2<>"" condition prevents the formatting rule from applying to empty cells. Without it, Excel might treat a blank date cell as zero (which is mathematically less than today) and incorrectly highlight empty rows.

Document & File Issues