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.

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.

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–

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

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

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

Similar Readings

• Excel Formula If Date Is Less Than Today (4 Examples)
• Find If Date Is Within 3 Months in Excel (5 Easy Ways)
• Excel Formula If Date Is Greater Than 2 Years (3 Examples)
• If Cell Contains Date Then Return Value in Excel (5 Examples)

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.

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

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.

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

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-

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.

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

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

• Check If Date Is Within 7 Days of Another Date in Excel (7 Ways)
• How to Use COUNTIF for Date Greater Than 30 Days in Excel
• Excel Formula If Date Is Greater Than 365 Days (4 Ideal Examples)
• How to Compare Dates to Today with Excel VBA (3 Easy Ways)
• Excel Formula If One Date is Greater Than Another Date