Function Problems

How to Highlight Dates Within 10 Days in Excel Using Conditional Formatting

Nimra Malik Sep 28, 2026 869 views

Question details

The user needs to use conditional formatting to highlight specific target dates that fall exactly within the next ten days from today's date.

How to Use Excel Conditional Formatting for Dates Within 10 Days

Product: Excel
Device & OS: not provided
Scenario: Tracking upcoming events, such as when crops will be ready for harvest, by dynamically highlighting dates approaching within a 10-day window.
Observed behavior: The user requires a specific formula to correctly trigger conditional formatting for a future target date that is exactly or within ten days ahead of the current date.

Before you start

Ensure the cells containing your target dates are formatted as actual Date values in Excel, rather than plain text, so the TODAY() function can calculate the difference accurately.

Solution 1Recommended

Use the AND Function to Highlight Upcoming Dates

Use a combined formula with the AND and TODAY functions to specifically target future dates that fall between today and exactly 10 days into the future.

This method is ideal for situations where you only want to see future dates approaching within a specific window, ignoring dates that have already passed.

Select the target range

Highlight the range of cells containing the dates you want to evaluate (for example, column L, starting from L6).

Create a New Formatting Rule

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

Enter the formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter =AND($L6>=TODAY(), $L6<=TODAY()+10). Note: Replace $L6 with the very first cell of your selected range.

Apply formatting

Click the 'Format' button, choose a background fill color (like green or yellow) to highlight the approaching dates, click 'OK', and apply the rule.

Use the AND Function to Highlight Upcoming Dates

Solution 2

Use a Subtraction Formula (Includes Past Dates)

A simpler formula can be used if you want to flag any date that is less than or exactly 10 days away, though it will also highlight dates in the past.

Efficient Date Formatting in WPS Spreadsheet

Easily Highlight Upcoming Dates with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting formulas, including dynamic date calculations using the TODAY() function. Manage your tracking spreadsheets and highlight upcoming deadlines seamlessly in a lightweight interface.

1. Select Data: Open your document in WPS Spreadsheet and highlight the cells containing your dates.
2. Open Conditional Formatting: Navigate to the Home tab and select 'Conditional Formatting', then click 'New Rule'.
3. Apply Formula: Choose the formula option, enter =AND($L6>=TODAY(), $L6<=TODAY()+10), pick a highlight color, and click 'OK'.

100% compatible with Microsoft Excel conditional formatting rules and formulas.Familiar, intuitive interface makes setting up data highlighting fast and easy.Lightweight design processes complex formulas smoothly without lag.

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the rule to highlight dates within 5 or 15 days?

Simply adjust the addition at the end of the formula. For a five-day window, use =AND($L6>=TODAY(), $L6<=TODAY()+5). For fifteen days, replace the +10 with +15.

Why is my conditional formatting applied to the wrong rows?

This usually happens if your cell references don't align correctly. Make sure the cell reference in your formula (e.g., $L6) exactly matches the top-left cell of the range you selected before creating the rule.

Can I highlight dates that have already passed?

Yes. To highlight dates that are earlier than today, create a new conditional formatting rule and use the formula =$L6<TODAY().

Excel & Data Problems