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.
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
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.
📕 Read More: Conditional Formatting Based on Another Cell Date in Excel
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.
📕 Read More: Conditional Formatting Based on Date Range in Excel
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.
📝 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
- Excel VBA: If Font Color Is Red Then Return Specific Output
- 6 Easy Ways to Highlight Lowest Value in Excel
- How to Apply Excel Formula to Change Text Color Based on Value
- Conditional Formatting to Highlight Row Based on Date in Excel