Formatting Issues

How to Set Excel Conditional Formatting Based on Days Between Two Dates

Partner Editor Oct 10, 2026 868 views

Question details

The user wants to apply conditional formatting to highlight cells based on the specific number of days elapsed between two dates.

How to Set Excel Conditional Formatting Based on Days Between Two Dates

Product: Microsoft Excel
Device & OS: not provided
Scenario: Formatting cells green when the date difference is between 98 and 112 days, and red when the difference falls outside of that range.
Observed behavior: Cells dynamically change color to green or red based on the calculated day difference between dates in column A and column B.

Before you start

Ensure both columns containing your data are properly formatted as Dates, and identify the exact starting row of your selected range so your formula references align correctly.

Solution 1Recommended

Use Custom Logical Formulas for Conditional Formatting

Apply custom AND/OR formulas in the conditional formatting rules to calculate date differences and assign colors based on specific date ranges.

By using custom formulas, you can set precise logical tests (such as checking if a date difference falls within a specified window) and manage what happens when cells are blank or contain non-date values.

Select the target range

Highlight the cells in your worksheet where you want the conditional formatting to apply, such as your data in Column B.

Create the Green rule

Navigate to Home > Conditional Formatting > New Rule. Select 'Use a formula to determine which cells to format'. Enter the formula =AND(B1-A1>=98,B1-A1<=112), click Format to choose a green fill color, and click OK.

Create the Red rule

Go back to Conditional Formatting > New Rule to create a second condition. Enter the formula =OR(NOT(ISNUMBER(A1)),NOT(ISNUMBER(B1)),B1-A1<98,B1-A1>112), choose a red fill color in the Format menu, and click OK.

Adjust row references and rule hierarchy

Ensure the row numbers in your formulas (like A1 and B1) match the very first row of your highlighted selection. Then, go to Home > Conditional Formatting > Manage Rules to place the green rule above the red rule.

Use Custom Logical Formulas for Conditional Formatting

Handling Blank Cells: The inclusion of NOT(ISNUMBER()) in the red rule formula ensures that empty cells or text values do not trigger incorrect formatting calculations.

Master Conditional Formatting with WPS Office

Easily Apply Date-Based Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet provides a highly intuitive interface for applying custom conditional formatting rules, allowing you to highlight date differences using the exact same formulas as Microsoft Excel.

1. Open your data in WPS Spreadsheet: Launch WPS Spreadsheet and open the document containing your date columns.
2. Access Conditional Formatting: Highlight your target cells, navigate to the Home tab, click on Conditional Formatting, and select New Rule.
3. Input your custom formula: Choose 'Use a formula to determine which cells to format', enter your custom date difference formula, set your desired fill color, and click OK to apply.

Fully compatible with Microsoft Excel conditional formatting formulas and rules.Lightweight software that runs smoothly on Windows, Mac, and Linux.Free to use with a user-friendly tabbed interface for efficient spreadsheet management.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows?

This usually happens when the cell references in your formula do not match the top row of your selected range. If you highlighted B2:B100, your formula must reference row 2 (e.g., =AND(B2-A2>=98, B2-A2<=112)), not row 1.

Does this conditional formatting formula work on Mac as well as Windows?

Yes, standard spreadsheet conditional formatting formulas, including date difference calculations and logical functions like AND/OR, work identically on both Windows and Mac versions.

Can I use DATEDIF instead of subtracting cells directly?

Yes, you can use the DATEDIF function to calculate the days between two dates. However, simple subtraction (e.g., B1-A1) is often preferred for calculating days because it is shorter and highly reliable across all spreadsheet software.

Document & File Issues