Formatting Issues

How to Highlight an Excel Row When Two Dates Are Within 5 Days

Elise Williams Oct 1, 2026 868 views

Question details

The user needs to highlight entire rows in Excel based on a condition where a date in column B is within 5 days of a date in column A.

How to Highlight an Excel Row When Two Dates Are Within 5 Days

Product: Excel
Device & OS: not provided
Scenario: Comparing two dates across different columns to visually flag the entire row if the difference between the dates is 5 days or less.
Observed behavior: A conditional formatting rule is applied using a mixed reference formula, which successfully colors the entire qualifying row while ignoring blank cells.

Before you start

Ensure your date columns are properly formatted as Dates in Excel, as conditional formatting formulas rely on numerical date values to calculate the differences accurately.

Solution 1Recommended

Use Conditional Formatting with a Mixed Reference Formula

Apply a formula-based conditional formatting rule to compare dates and highlight the qualifying rows while preventing empty rows from being colored.

To highlight an entire row based on the values in specific columns, you must use a mixed reference in your formula (e.g., $A2). This locks the column so Excel evaluates the date columns correctly while allowing the row number to change as it applies the format down the dataset.

Select the data range

Highlight the entire range of cells you want to format. Ensure the active cell is in the first row of your selection (for example, row 2 if your data starts below the headers).

Open Conditional Formatting

Navigate to the Home tab on the Excel 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 that says 'Use a formula to determine which cells to format'.

Enter the date comparison formula

In the formula bar, enter =AND($A2<>"",$B2<>"",$B2<=$A2+5). This formula checks if the date in column B is within 5 days of column A, while the AND condition ensures that blank cells are not accidentally highlighted.

Set the fill color and apply

Click the Format button, navigate to the Fill tab, choose your desired highlight color, and click OK twice to apply the formatting rule to your worksheet.

Use Conditional Formatting with a Mixed Reference Formula

Formula Customization: If you want to check if the dates are exactly 5 days apart rather than within 5 days, you can change the less-than-or-equal operator (<=) to an equals sign (=) in the formula.

Free Advanced Spreadsheet Tool

Highlight Rows Based on Dates Easily in WPS Office

WPS Spreadsheet offers robust conditional formatting tools that are fully compatible with Excel formulas. You can seamlessly compare dates and highlight rows using the exact same mixed references and functions in a fast, lightweight environment.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the date columns.
2. Select the data range: Highlight all the rows and columns you wish to apply the fill color to, starting from the first data row.
3. Access Conditional Formatting: Go to the Home tab on the top ribbon, click Conditional Formatting, and select New Rule.
4. Apply the formula and format: Select 'Use a formula to determine which cells to format', enter =AND($A2<>"",$B2<>"",$B2<=$A2+5), choose a color, and save.

Fully compatible with Microsoft Excel conditional formatting and formulas.Free and lightweight alternative for powerful data analysis and visualization.Intuitive interface to manage multiple formatting rules effortlessly.Seamless migration of your existing Excel workbooks without losing formatting.

Frequently Asked Questions

Why is my conditional formatting highlighting blank rows?

Blank cells are often treated as zero by Excel, which can cause date comparison formulas to trigger incorrectly. To prevent blank rows from being highlighted, wrap your date comparison in an AND function to ensure the cells are not empty, like =AND($A2<>"", $B2<>"", $B2<=$A2+5).

What does the dollar sign ($) do in the conditional formatting formula?

The dollar sign creates a mixed reference (e.g., $A2), locking the column while allowing the row to change dynamically. This forces the spreadsheet to evaluate those specific date columns for the condition but applies the resulting fill color across the entire row.

Can I change the number of days in the condition?

Yes, you can easily modify the number in the formula to fit your needs. For example, to highlight rows where the date difference is within 10 days, simply change the formula to =AND($A2<>"",$B2<>"",$B2<=$A2+10).

Document & File Issues