4 Ways to Use Conditional Formatting for Dates Overdue in Excel

In this lesson, I’ll demonstrate 4 effective approaches to using conditional formatting for dates overdue in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

Learn to Use Conditional Formatting for Dates Overdue in Excel with These 4 Useful Techniques

Here in this article, we will learn how to use conditional formatting for dates overdue in Excel using different approaches. To be exact, I’ve provided a total of 4 methods down below.

Method 1

1. Setting Greater Than Condition of Conditional Formatting

Here, we have the dataset for students and their submission date of an assignment. Here we are learning how to use conditional formatting for dates overdue setting a basic condition. For instance, here we want to know the cells which are past the deadline follow the instructions below.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, Select the cells you want to format.

• Now go to Conditional Formatting from the Home tab of the ribbon. Then select Greater Than from the Highlight Cells Rules.

• Then a Dialogue Box will pop up select the cells containing deadlines here or you can manually input. Besides this, you will see an option which is the color you want to convert into select a color and then press OK.

• So the final results will be like this.

📕 Read More: 5 Easy Ways to Highlight Highest Value in Excel

Method 2

2. Defining Rule with TODAY Function

Here, we are going to use the New Rule option of Conditional Formatting. We will use the TODAY function to set a new rule here. Here the deadline was 10 days before today. So we are filtering the dates that are within this period.

⬇️⬇️ STEPS ⬇️⬇️

• First, Select the cells you want to format.

• Now go to Conditional Formatting from the Home tab of the ribbon. Then select the New Rule.

• For instance, say the last submission day was 10 days before today. So we set the rule as below. And select greater than and Format the cell as per our demand then click OK.

=TODAY()-10

• After that, we will get the following results.

Method 3

3. New Rule with Existing Date

Here we are using the New Rule feature of the Conditional Formatting but with another formula. Let’s say Christopher submitted 5 days before the deadline. Now we want to filter the cells that are past the deadline..

⬇️⬇️ STEPS ⬇️⬇️

• First, Select all the cells you want to format.

• Now go to Conditional Formatting from the Home tab of the ribbon. Then select the New Rule.

• For instance, say the last submission day was 5 days after Christopher submitted the assignment. So we set the rule as below. And select greater than and Format the cell as per our demand then click OK.

=\$C\$8+5

• After clicking OK, you’ll see the following results.

Method 4

4. Using A Date Occurring Option

Now we will use the A Date Occurring feature of the Conditional Formatting. If we want some exact criteria then we can use this feature. Like here we are demonstrating how to highlight the dates that are within the last month. You will see other different criteria that can be used by this feature following the instructions.

⬇️⬇️ STEPS ⬇️⬇️

• First, Select all the cells you want to format.

• Now go to Conditional Formatting from the Home tab of the ribbon. Then select the A Date Occurring option from the Highlight Cells Rules.

• Now select the criteria, we are selecting Last month as we want the list of students who submitted the assignment last month, you will see different other criteria here. After that, choose the formatting color on the right side of with and press OK.

• You’ll see the following results.

📄 Important Notes

`🖊️`  Use shortcuts to reduce the time for instance F4 for absolute value

`🖊️`  While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

`🖊️`  Be careful while defining criteria to be accurate as the dataset.

`📌`  This article focuses on finding the overdue dates with the help of conditional formatting in Excel.

`📌`  Here we used the TODAY function to set a new rule for formatting cells that are past the deadline which we are defining here with TODAY function.

`📌`  We have also used the Greater Than feature of Conditional Formatting to find the overdue dates.

`📌`  Then we set a New Rule to achieve the same result.

`📌`  Using A Date Occurring option to filter cells that are from the last month.