# Excel Conditional Formatting Based on Past or Due Date

Get FREE Advanced Excel Exercises with Solutions!

Finding out past due dates by highlighting them using Excel Conditional Formatting is quite time-saving, easy, and fast. There are a lot of ways to do it but this article will show you the best 5 methods to use Conditional Formatting based on the date past due in Excel with easy steps and clear illustrations.

First of all, get introduced to our dataset that represents some salespersonsâ€™ due dates.

## 1. Using Excel TODAY Function to Get Past or Due Date

In our first method, weâ€™ll highlight the dates past due by using the TODAY function. It will show TRUE or FALSE for the output.

Steps:

• Insert the following formula in Cell D5â€“
`=C5<TODAY()`
• Then just press the ENTER button for the output.

• Later, use the Fill Handle tool to copy the formula.

Now see, for the past or due dates, it is showing TRUE.

Read More:Â How to Compare Dates in Two Columns in Excel

## 2. Use of Excel Conditional Formatting to Highlight Date Based on Past or Due Date

In Excel Conditional Formatting there is Less Than command by which we can highlight a value that is less than any value. Weâ€™ll apply for due dates here.

Steps:

• Select the date range.
• Next click as follows: Home > Conditional Formatting > Highlight Cells Rules > Less Than.

• You will get a dialog box like this. Insert the following formula in the Format cells that are LESS THAN boxâ€“
`=TODAY()`
• Then choose the highlight color from the second box and press OK.

Soon after, the dates less than today will be highlighted with our selected color.

## 3. Using New Rule Command to Highlight Past Due Date in Excel

Using the New Rule command we can do the same operation by using a formula and here we can customize the highlight color with a lot of customizations.

Steps:

• First, click as follows: Home > Conditional Formatting > New Rule.

• After that, select Use a formula to determine which cells to format from the Select a Rule Type box.
• Next, type the following formula in the Format values where this formula is true box-
`=C5<TODAY()`
• Later, click the Format button. It will open the Format Cells dialog box.

• Form the Fill section, and choose a color. I picked lite yellow color.
• Then click OK and it will take you back to the previous dialog box.

• Nothing to do more, just press OK.

The cells are now highlighted with the selected color.

## 4. Applying â€˜A Date Occurringâ€™ Command to Highlight Date Past Due

For date formatting in Excel, there is a dedicated command named, A Date Occurring. It has different default options which we can apply for conditional formatting based on the date past due.

Steps:

• Click as follows: Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring.

• From the first drop-down box, choose the right option for your dates. I chose Last month.
• Next, select the highlight color from the second box and press OK.

It is now highlighting the past due dates of the last month.

## 5. Highlighting Specific Day Differences for Date with Conditional Formatting in Excel

In this last method, weâ€™ll apply Conditional Formatting for specific day differences. For that, we modified the dataset a little bit- a column for due dates and another column for the current dates. For current dates, we used todayâ€™s date.Â  Weâ€™ll set different colors for different ranges as shown in the image below-

First, weâ€™ll find the day difference.

Steps:

• Insert the following formula in Cell E5 â€“
`=D5-C5`
• Then use the Fill Handle tool for the other cells.

• Select the dates from the Current Date column and then follow the first step from the third method to apply a rule.
• Then select Use a formula to determine which cells to format from the Select a Rule Type box.
• Later, type the following formula in the Format values where this formula is true box-
`=(D5-C5)<3`
• Click the Format button. It will open the Format Cells dialog box.

• Choose the fill color from the Fill section as mentioned in the dataset.
• Then press OK.

• Then after returning back to the previous dialog box, press OK.

Hereâ€™s the output-

• After that, follow the same steps to apply the other two conditions.

Use the following formula correspondingly for yellow color and biscuit color:

`=(D5-C5)<15`
`=(D5-C5)>15`

Then the final output will like this-

You can download the free Excel workbook from here and practice independently.

## Conclusion

Thatâ€™s all for the article. I hope the procedures described above will be reasonable enough for Conditional Formatting based on the date past due. Feel free to ask any questions in the comment section and give me feedback.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF