Mrinmoy Roy
You may want to highlight the past dates from a specific date (It can be today or any other day) in Excel. If it’s so, then you have a relax. Because Excel offers several ways that you can use to apply conditional formatting to highlight overdue dates. To learn all of those ways, go through the whole article because you will be learning 3 different ways to highlight overdue dates using conditional formatting in Excel with ease.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
The TODAY Function: an Overview
The TODAY function produces the current date. If you insert the TODAY function within a cell where the cell is General by default, after applying the TODAY function, it is changed automatically to Date.
Syntax
TODAY()
The TODAY function syntax has no arguments.
3 Ways to Apply Conditional Formatting to the Overdue Dates in Excel
In this article, we will be using a project deadline list as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:
So, without having any further discussion let’s dive straight into all the methods one by one.
1. Apply Conditional Formatting to the Overdue Dates Using Less Than Command in Excel
Suppose, you want to highlight all the cells containing dates before a specific date. If it’s so, then you can easily do it using the Less Than command in Excel. To do so,
❶ Select the range of cells where you want to apply the formatting.
❷ Go to Home ▶ Conditional Formatting ▶ Highlight Cells Rules ▶ Less Than.
After all the steps above, the Less Than dialog box will appear. In the box, insert a date based on which all the cells containing dates before it will be highlighted with color.
For instance, we’ve inserted the date as of 2/18/2021. Now hit the Ok command.
After hitting the Ok command, you will get all the intended cells highlighted with color as in the image below:
Read more: Excel Conditional Formatting Dates Older than Today (3 Simple Ways)
2. Use Today Function to Apply Conditional Formatting to the Overdue Dates in Excel
Now let’s apply conditional formatting to all overdue dates in Excel. We can do it easily using the Today function and the using New Rule command in the Conditional Formatting group. To do it,
❶ Select the range of cells where you want to apply the conditional formatting.
❷ Then go to Home ▶ Conditional Formatting ▶ New Rule.
At this point, a New Formatting Rule dialog box will pop up. From the dialog box,
❶ Select Format only cells that contain.
❷ After that select less than under Format only cells with field and insert function
=TODAY()❸ Then choose format color using the Format option.
❺ Finally hit Ok.
When you are done with all of the steps above, you will get all the cells highlighted with orange color.
Read more: How to Use Conditional Formatting in Excel Based on Dates
3. Use New Rule to Apply Conditional Formatting to the Overdue Dates in Excel
We can use a formula by creating a new rule to highlight overdue dates with color to avail more complicated criteria. For example, from our project deadline list, we want to highlight those dates that differ more than 10 days as to another inserted date which is 1/5/2021. To do so,
❶ Select the range of cells where you want to apply the conditional formatting.
❷ Then go to Home ▶ Conditional Formatting ▶ New Rule.
After that, a New Formatting Rule dialog box will appear. From the box,
❶ Select Use a formula to determine which cells to format.
❷ After that, insert formula
=$C$16-C5>10❸ Choose a formatting color using the Format command.
❹ Finally, hit the Ok button.
When you are done with all of these, you will see your intended cells are highlighted with the color that you’ve chosen using the Format command as in the picture below:
Read more: Excel Conditional Formatting for Dates within 30 Days
Things to Remember
📌 Always select the cells before applying the Conditional Formatting command.
📌 Press CTRL + Z to undo the Conditional Formatting command.
Conclusion
To wrap up, we have illustrated 3 methods to apply conditional formatting to the overdue dates in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
How do I unhighlight an overdue date once the task is complete. So column b is the due date. It automatically turns red when it is past todays date. If I add the completed date in column c, how can I automatically unhighlight the column b cell to show it’s not loger overdue?
Hi Les,
Conditional Formatting is a static feature. Being a static feature, it doesn’t update itself automatically. However, you can apply the conditional formatting again with the default cell color to unhighlight all the completed dates.
Regards!
Hello – How can I use conditional formatting to display a date in red only if, a) the status in another column does not equal complete, and b) the date is less than or equal to today?
Column D contains my Due Date
Column F contains the Completion Status
Basically, I’m trying do this but don’t know how: If the date in D is today or older AND the status does not equal complete then I want the date to turn red.
Also, would like to gray out any dates with a status of complete.
Hello TAB,
Thanks for your feedback. You can easily do that by using a simple formula.
Follow the steps:
1. Select the range of dates.
2. Click on the Conditional Formatting command from the Home tab.
3. Then select New Rule.
4. Select “Use a formula to determine which cells to format”.
5. After that, insert the formula in the “Format values where this formula is true box”-
=AND(D1<=TODAY(),F1<>"Complete")6. Choose the Red fill color from the Format command.
7. Finally, hit the OK button.
*To gray out the dates with complete status, use the following rule and Gray fill color:
=AND(D1<=TODAY(),F1="Complete")