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.


Download Practice Workbook

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


5 Ways to Use Conditional Formatting Based on Past or Due Date in Excel

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

How to Use Conditional Formatting Based on Past or Due Date in Excel


1. Using TODAY Function to Highlight 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.

Using TODAY Function to Highlight Past or Due Date

Using Fill Handle to Highlight Past or Due Date

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

Read More: Conditional Formatting Based on Date Older Than 1 Year in Excel


2. Using Less Than Command of Conditional Formatting to Highlight 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.

Using Less Than Command of Conditional Formatting to Highlight Date

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

Insert Formula in Less Than Command of Conditional Formatting to Highlight Date

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

Read More: Conditional Formatting for Dates Older Than Certain Date in Excel


Similar Readings


3. Using New Rule Command of Conditional Formatting to Highlight Past Due Date

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.

Using New Rule Command of Conditional Formatting to Highlight Past Due Date

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

Using Formula in New Rule Command of Conditional Formatting to Highlight Past Due Date

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

Choosing Fill Color in New Rule Command of Conditional Formatting to Highlight Past Due Date

  • Nothing to do more, just press OK.

The cells are now highlighted with the selected color.

Read More: How to Compare Dates in Two Columns in Excel (8 Methods)


4. Using ‘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.

Using ‘A Date Occurring’ Command to Highlight Date Past Due

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

Selecting Date in ‘A Date Occurring’ Command to Highlight Date Past Due

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

Read More: How to Compare If Date Is Before Another Date in Excel


5. Applying Conditional Formatting to Highlight Specific Day Difference for Date

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-

Applying Conditional Formatting to Highlight Specific Day Difference for Date

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.

Calculating day difference

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

Applying Formula in Conditional Formatting to Highlight Specific Day Difference for Date

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

Read More: Excel Conditional Formatting for Date Within 3 Months (3 Methods)


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.


Related Articles

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo