Formatting Issues

How to Use Excel Conditional Formatting for Due Dates Within 30 Days and Overdue Tasks

Aamir Naveed Akram Oct 19, 2026 869 views

Question details

The user wants to format employee review due dates dynamically based on the current date, coloring overdue dates red and dates within the next 30 days orange, while ignoring prior years.

How to Use Excel Conditional Formatting to Highlight Overdue Dates and Upcoming Deadlines

Product: Excel
Device & OS: not provided
Scenario: Tracking employee review due dates or task deadlines to ensure timely completion using dynamic date functions.
Observed behavior: Applying dynamic conditional formatting rules using TODAY() functions to correctly categorize and visually distinguish upcoming deadlines, overdue tasks, and past records.

Before you start

Ensure your target cells are formatted as Dates rather than Text, and clear any conflicting conditional formatting rules in the selected range before applying new ones.

Solution 1Recommended

Use Formula-Based Conditional Formatting (Recommended)

Create custom rules using the AND and TODAY functions to precisely target upcoming and overdue dates while safely ignoring blank cells and irrelevant past years.

This method is highly recommended because using formulas gives you exact control over complex conditions, such as ignoring blank cells which Excel otherwise evaluates as 0 (a past date).

The formulas below assume your selected range begins at cell A1. If your data starts at a different cell (e.g., B2), change 'A1' in the formula to match your starting cell.

Select the target range

Highlight the entire range of cells containing the due dates you want to format.

Open Conditional Formatting

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

Create the rule for Overdue dates

Select 'Use a formula to determine which cells to format'. Enter the formula: =AND(A1<>"",A1>=DATE(YEAR(TODAY()),1,1),A1<TODAY()). Click 'Format', choose a red fill color on the Fill tab, and click OK twice.

Create the rule for Due Within 30 Days

Click 'Conditional Formatting' > 'New Rule' again. Use this formula: =AND(A1<>"",A1>TODAY(),A1<=TODAY()+30). Click 'Format', choose an orange fill color, and save.

Use Formula-Based Conditional Formatting (Recommended)

Rule Priority: If you add multiple conditional rules, ensure they are placed in the correct order in the 'Manage Rules' dialog. Overdue rules should typically sit at the top, though the specific AND functions used here make the conditions mutually exclusive.

Solution 2

Use Standard Cell Value Rules

Apply simpler cell value conditions using the built-in conditional formatting dropdowns, prioritizing rules by order.

Track Deadlines with WPS Spreadsheet

Highlight Due Dates in WPS Office

WPS Spreadsheet fully supports advanced conditional formatting and functions like TODAY(), allowing you to effortlessly track deadlines and overdue tasks with customizable color rules.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the employee review or project due dates.
2. Select the date range: Highlight the specific cells or column where your due dates are located.
3. Apply Conditional Formatting: Go to the Home tab, click 'Conditional Formatting', and select 'New Rule'.
4. Enter the rule parameters: Choose 'Use a formula to determine which cells to format', enter your TODAY() formula, and select your desired warning colors.

100% compatible with Microsoft Excel (.xlsx) formulas, formatting, and date calculations.Free to use with a familiar, user-friendly interface that requires no learning curve.Built-in conditional formatting templates for quick and dynamic data visualization.

microsoft office alternative - wps office

Frequently Asked Questions

Why are blank cells being highlighted as overdue?

Spreadsheet software evaluates empty cells as the number 0, which translates to the date January 0, 1900. Since this is in the past, it triggers 'less than TODAY()' rules. To fix this, add the condition A1<>"" within an AND function to ensure the cell is not blank.

How do I make the formatting apply to the entire row instead of just the date cell?

To highlight the entire row, select your entire data table instead of just the date column. When writing your formula, lock the column reference for the date cell by adding a dollar sign (e.g., =$A1<TODAY()). This forces the rule to evaluate the date column for every cell in the row.

How do I highlight dates that are exactly due today?

Create a new Conditional Formatting rule using 'Format only cells that contain', set the condition to 'equal to', and enter the formula =TODAY(). Alternatively, use the 'Use a formula' option with the formula =A1=TODAY().

Will these rules automatically update tomorrow?

Yes. The TODAY() function is volatile and dynamically recalculates based on your computer's system clock. Every time you open the file or calculate the sheet, the dates will automatically update to reflect the new current day.

Document & File Issues