Excel Conditional Formatting Based on Past or Due Date

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–

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

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–

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

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

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

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

Then the final output will like this-

Download Practice Workbook

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.

<< Go Back to Dates | Compare | Learn Excel