Formula Errors

How to Highlight SLA Date Ranges Using Conditional Formatting in Excel

Huda Qurayshi Oct 20, 2026 868 views

Question details

The user needs to apply conditional formatting to color-code SLA date cells based on the number of days elapsed since the creation date.

How to Highlight SLA Date Ranges Using Conditional Formatting in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking Service Level Agreement (SLA) compliance by visually highlighting the time elapsed between a ticket's creation date and its SLA completion date.
Observed behavior: The goal is to format SLA cells green when the difference is 5 days or less, yellow for 6 to 15 days, and red when it exceeds 15 days.

Before you start

Ensure that the cells in your creation date and SLA date columns are properly formatted as Dates, rather than text, so the mathematical difference can be calculated accurately.

Solution 1Recommended

Apply Custom Formula Rules for SLA Conditional Formatting

Use the 'New Rule' feature with custom AND formulas to calculate the day difference and apply distinct colors based on your SLA thresholds.

By using the AND function within your conditional formatting rules, you can verify that both date cells contain data before performing the calculation. This prevents empty cells from being incorrectly highlighted.

Select the target cell

Click on the first SLA date cell in your column (for example, E2) where you want to apply the formatting.

Create the Green rule

Navigate to Home > Conditional Formatting > New Rule. Choose 'Use a formula to determine which cells to format'. Enter the formula: =AND(E2<>"",D2<>"",E2>D2,E2-$D2<=5). Click Format, select a green fill color, and click OK.

Create the Yellow rule

Repeat the process by clicking New Rule again. Enter the formula for the yellow condition: =AND(E2<>"",D2<>"",E2>D2,E2-$D2>5,E2-$D2<=15). Click Format, choose a yellow fill color, and click OK.

Create the Red rule

Add the final rule by entering the formula: =AND(E2<>"",D2<>"",E2>D2,E2-$D2>15). Click Format, select a red fill color, and click OK.

Apply rules to the entire column

Select cell E2, click the Format Painter tool on the Home tab, and drag it down your SLA column to apply these formatting rules to the rest of your data.

Apply Custom Formula Rules for SLA Conditional Formatting

Handling Blank Cells: The portions of the formula reading E2<>"" and D2<>"" ensure that Excel checks that both dates are present before calculating the difference, preventing blank cells from triggering false alerts.

Efficient Data Management

Highlight SLA Dates Easily with WPS Spreadsheet

WPS Spreadsheet fully supports Excel conditional formatting formulas. You can seamlessly apply multi-condition rules to track deadlines visually, all within a lightweight and free application.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your SLA tracking data.
2. Select the SLA column: Highlight the specific column or cells where your SLA dates are located.
3. Access Conditional Formatting: Navigate to the Home tab on the top ribbon and click on Conditional Formatting.
4. Input custom formulas: Select 'New Rule', choose the formula option, and input your date-difference formulas.
5. Apply colors: Set your desired highlight colors for each rule and click OK to apply the changes instantly.

Fully compatible with Microsoft Excel conditional formatting and custom formulasLightweight application that runs smoothly on older devicesFree to use for everyday data tracking and spreadsheet management

microsoft office alternative - wps office

Frequently Asked Questions

Why are blank cells getting highlighted by my conditional formatting rules?

Blank cells evaluate as zero in Excel formulas, which can inadvertently trigger mathematical rules. To prevent highlighting blank cells, always include conditions like E2<>"" and D2<>"" in your AND formula to ensure formatting applies only when both dates actually exist.

Can I apply conditional formatting to the entire row instead of just the SLA date cell?

Yes. Select the entire data range before creating the rule, and lock the column references in your formula by adding a dollar sign (e.g., =$E2-$D2<=5). This anchors the calculation to the specific date columns while coloring the whole row.

How do I change the SLA day thresholds later without re-entering the formulas?

Go to Home > Conditional Formatting > Manage Rules. Select the rule you wish to edit from the list, click Edit Rule, and simply update the number of days directly in the formula text box.

Excel & Data Problems