Apply Conditional Formatting for Dates Older Than Today in Excel

Sometimes we need to highlight or count dates under different conditions in MS Excel. For this purpose, Excel provides us with great approaches to using date formatting depending on different conditions. This article will show how to use conditional formatting to highlight dates older than today, or present-day, in Excel. If you are also curious about it, download our practice workbook and follow us.

Apply Conditional Formatting for Dates Older Than Today in Excel: 3 Suitable Examples

To demonstrate the examples, we consider a shipment date of 10 items. The name of the items is in column B, their delivery time is in column C. So, our dataset is in the range of cells B5:C14. We get today’s date in cell D5 using the TODAY function.

1. Apply Simple Conditional Formatting Method to Highlight Dates Older Than Today

In this example, we are going to use the simple method for applying conditional formatting to highlight dates older than today. The steps to finish this example are given below:

📌 Steps:

• First of all, select the range of cells C5:C14.
• In the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group and choose the New Rules option.

• As a result, a small dialog box called the New Formatting Rule will appear.
• Now, select the Format only cells that contain option.
• Then, in the second field, change the option from between to less than.

• After that, in the next empty field, write down the following formula using the TODAY function.

• Next, click the Format button.

• Another dialog box called Format Cells will appear.
• Now, in the Fill tab, choose a cell color according to your desire. For our case, we choose the Orange, Accent 2, Lighter 40% color.
• Click OK to close the dialog box.

• Again, click OK to close the New Formatting Rule dialog box.

• You will see the dates which are older than today will get our desired cell color.

Thus, we can say that our process works perfectly, and we are able to apply conditional formatting to highlight dates older than today in Excel.

Read More: Highlight Row with Conditional Formatting Based On Date in Excel

2. Apply Conditional Formatting for Dates Older Than Today Using Range

In the second example, we will use our simple method to apply conditional formatting to highlight dates older than 10 days from today. The steps to complete this example are given as follows:

📌 Steps:

• First, select the range of cells C5:C14.
• Now, in the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group and choose the New Rules option.

• As a result, a small dialog box called the New Formatting Rule will appear.
• Then, select the Use a formula to determine which cells to format option.

• After that, in the empty field below the text Format values where this formula is true, write down the following formula using the TODAY function.

• Afterward, click the Format button.

• Another dialog box called Format Cells will appear.
• Next, in the Fill tab, choose a cell color according to your desire. We choose the Orange, Accent 2, Lighter 40% color.
• Finally, click OK.

• Similarly, click OK to close the New Formatting Rule dialog box.

• You will see the dates which are older than 10 days from today will be highlighted

Hence, we can say that our method works effectively, and we are able to apply conditional formatting to highlight dates older than today in Excel.

Read More: Excel Conditional Formatting Based on Date

3. Highlight Dates Using Conditional Formatting Between Today and 30 Days Ago (Range of Dates)

In the last example, we are going to demonstrate the procedure for applying conditional formatting to highlight dates older not less than 30 days from today. The steps to accomplish this example are given below:

📌 Steps:

• At first, select the range of cells C5:C14.
• After that, in the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group and choose the New Rules option.

• As a result, a small dialog box called the New Formatting Rule will appear.
• Then, select the Format only cells that contain option.

• Now, in the third empty box, write down the following formula using the TODAY function.

• Next, in the next empty box, write down the following formula with the TODAY function.

• Afterward, click the Format button.

• Another dialog box called Format Cells will appear.
• Then, in the Fill tab, choose a cell color according to your desire. In this case, we choose the Orange, Accent 2, Lighter 40% color.
• At last, click OK to close the dialog box.

• Again, click OK to close the New Formatting Rule dialog box.

• You will see the dates lie between 30 days from today and get the highlighting color.

Finally, we can say that our approach works precisely, and we are able to apply conditional formatting to highlight dates older than 30 days from today in Excel.

Read More: How to Change Cell Color Based on Date Using Excel Formula

Apply Conditional Formatting for Dates within 90 Days

Now, we are going to show the approach for applying conditional formatting to highlight dates older not less than 90 days from today. The steps to highlight cells with the criteria are explained below:

📌 Steps:

• Firstly, select the range of cells C5:C14.
• Next, in the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group.
• Then, select the Highlight Cell Rules > Between option.

• As a result, a small dialog box called Between will appear.
• Now, in the first box, remove the default date and write down the following formula using the TODAY function.

• Next, in the next value box, write down the following formula with the TODAY function.

• Finally, click OK to close the dialog box. Here, we kept the default cell format. If you want your custom format, select the Custom Format option from the drop-down arrow and create your custom formatting as we showed in our previous examples.

• You will notice the dates lie between 90 days from today and get the highlighting color.

So, we can say that our working process works properly, and we are able to apply conditional formatting to highlight dates older than 90 days from today in Excel.

Read More: Excel Conditional Formatting for Dates within 30 Days

Perform Conditional Formatting for Dates Within 6 Months from Today

Here, we will explain the procedure for applying conditional formatting to highlight dates older not less than 180 days or 6 months from today. The steps to highlight cells with the following criteria are described below:

📌 Steps:

• First of all, select the range of cells C5:C14.
• In the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group.
• Now, select the Highlight Cell Rules > Between option.

• As a result, a small dialog box called Between will appear.
• Afterward, in the first value box, remove the default date and write down the following formula using the TODAY function.

• Then, in the next value box, write down a similar type of formula with the TODAY function.

• At last, click OK to close the dialog box. To format our cells, we kept the default cell format. If you want your custom format, select the Custom Format option from the drop-down arrow and create your custom formatting as we showed in our previous examples.

• You will get the dates with the default highlighting color.

In the end, we can say that our working approach works perfectly, and we are able to apply conditional formatting to highlight dates older than 180 days or 6 months from today in Excel.

Use Conditional Formatting for Dates Within 1 Year

In the last criterion, we are going to demonstrate the process for applying conditional formatting to highlight dates older not less than 1 year or 365 days from today. The step-by-step procedure to highlight cells with the following criteria is explained as follows:

📌 Steps:

• At first, select the range of cells C5:C14.
• Now, in the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group.
• Then, select the Highlight Cell Rules > Between option.

• As a result, a small dialog box called Between will appear.
• After that, in the first value box, remove the default date and write down the following formula using the TODAY function.

• Next, in the next value box, write down a similar type of formula with the TODAY function.

• In the end, click OK to close the dialog box. In our case, we kept the default cell format. If you want your custom format, select the Custom Format option from the drop-down arrow and create your custom formatting as we showed in our previous examples.

• You will get the dates with the default highlighting color.

Finally, we can say that our working method works successfully, and we are able to apply conditional formatting to highlight dates older than 365 days or 1 year from today in Excel.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to apply conditional formatting dates older than today in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Related Articles

• Conditional Formatting Based on Date in Another Cell in Excel
  
• Apply Conditional Formatting to Overdue Dates in Excel
  
• How to Apply Conditional Formatting to Each Row Individually
• How to Apply Conditional Formatting to Multiple Rows
• How to Highlight Row Using Conditional Formatting
• Conditional Formatting on Multiple Rows Independently in Excel
• How to Change Row Color Based on Text Value in Cell in Excel