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.


📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.


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.

all dates selected

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

selecting greater than option for conditional formatting dates overdue in excel

  • 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.

excel conditional formatting dates overdue

  • So the final results will be like this.

due dates highlighted with greater than option

📕 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.

Selecting the cells

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

selecting new rule for conditional formatting dates overdue in excel

  • 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

today function for conditional formatting dates overdue in excel

  • After that, we will get the following results.

due dates highlighted with today function

📕 Read More: Conditional Formatting Based on Another Cell Date in Excel

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.

all dates selected

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

selecting new rule option for highlighting

  • 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

custom formula for conditional formatting dates overdue in excel

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

due dates highlighted with formula

📕 Read More: Conditional Formatting Based on Date Range in Excel

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.

all dates selected

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

selecting a date occurring option for conditional formatting dates overdue in excel

  • 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.

a date occurring particulars

  • You’ll see the following results.

due dates highlighted with a date occurring option


📄 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.


📝 Takeaways from This Article

📌  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.


Conclusion

Firstly, I hope you were able to use the techniques I demonstrated in this How to use conditional formatting for dates overdue in Excel lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly I advise repeating the steps if you become confused in any of the steps if you get stuck. However, practice on your own after taking a look at the Excel file in the practice workbook I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.


Related Articles

(Visited 47 times, 1 visits today)
Nashid

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo