Conditional Formatting to Highlight Overdue Dates in Excel

Tracking overdue dates in Excel is crucial for staying on top of deadlines whether you’re managing invoices, tasks, or project milestones. Instead of manually scanning your sheet, Excel’s conditional formatting can instantly flag overdue dates by highlighting them based on today’s date.

In this article, we’ll show you multiple ways to highlight overdue dates in Excel using built-in rules, custom formulas, and dynamic conditions. Each method is quick to set up and works in real-time.

If you only need to highlight cells that contain past dates, this method is ideal. It uses the TODAY function to compare each due date against the current date. The rule automatically updates each day without needing any manual adjustment.

Steps:

➤ Select the cells with due dates (e.g., C2:C12).
➤ Go to Home >> Conditional Formatting >> New Rule.

➤ Choose “Use a formula to determine which cells to format.”
➤ Enter the formula:

➤ Click Format, set a fill color (e.g., green), and press OK.

This rule will highlight all dates that are before today. The formatting updates automatically each day.

When managing task progress, you may want to highlight only the tasks that are overdue and still pending. This formula allows you to add an extra condition by checking if the task’s status is “Pending” alongside the due date.

Steps:

➤ Select the cells with due dates (e.g., C2:C12).
➤ Go to Home >> Conditional Formatting >> New Rule.

➤ Enter the formula:

➤ Choose a relevant fill color from Format and click OK.

Now the overdue date is highlighted in green.

This method highlights the full row when a task is overdue and not marked as “Done.” It helps you scan your spreadsheet more easily by highlighting the complete task info across all columns, not just the due date.

Steps:

➤Select the range A2:D12.
➤Go to Home >> Conditional Formatting >> New Rule.

➤Choose “Use a formula to determine which cells to format“.
➤Enter the formula:

➤Click Format, choose a fill color (e.g., green), and click OK.

This will highlight the entire row for overdue tasks that are not marked as “Done“.

This is a quick and simple method if you only want to highlight past dates without worrying about formulas. It uses Excel’s built-in conditional formatting options and automatically compares each cell to today’s date using the “Less Than” rule.

Steps:

➤ Select the due date range (C2:C12).
➤ Go to Home >> Conditional Formatting >> Highlight Cells Rules >> Less Than.

➤ In the input box, type:

➤ Pick a formatting style or set a custom one.
➤ Click OK.

This method is great for simple overdue highlighting, but doesn’t allow complex conditions like status checks.

To proactively track upcoming deadlines, use this method to highlight tasks that are due within the next 7 days. It ensures you’re aware of near-future dates, including today, so you can prepare for upcoming work.

Steps:

➤ Select the cells with due dates (e.g., C2:C12).
➤ Go to Home >> Conditional Formatting >> New Rule.

➤ Choose “Use a formula to determine which cells to format“.
➤ Enter the formula:

➤ Click Format, choose a fill color (e.g.,green), and click OK.

This will highlight tasks that are due within the next 7 days.

Frequently Asked Questions

How can I highlight tasks that are due today?

To highlight tasks due today, use the formula =C2=TODAY() in your conditional formatting rule. This will highlight cells where the due date matches today’s date.

Can I highlight tasks that are due within the next 3 days?

Yes, use the formula =AND(C2>=TODAY(), C2<=TODAY()+3) in your conditional formatting rule. This will highlight tasks due within the next three days.

How do I remove conditional formatting from a range?

Select the range, go to Home >> Conditional Formatting >> Clear Rules, and choose either Clear Rules from Selected Cells or Clear Rules from Entire Sheet.

Can I apply multiple conditional formatting rules to the same range?

Yes, you can apply multiple rules. Excel will evaluate them in order, and you can manage the order and precedence in the Conditional Formatting Rules Manager.

How do I highlight overdue tasks only if the status is “Pending”?

Use the formula =AND(C2<TODAY(), D2=”Pending”) in your conditional formatting rule. This will highlight overdue tasks that are specifically marked as “Pending“.

Wrapping Up

In this tutorial, we learned how to highlight overdue dates in Excel using simple formulas, built-in rules, and logic conditions. Whether you’re tracking projects or tasks, these methods help you quickly identify what needs attention. Feel free to download the sample file and share your feedback.