Apply Conditional Formatting for Dates Older Than Today in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

The TODAY function will change the date automatically when you open the file on your device. So, the image of this article may not match your result. Don’t get panic. Just follow the procedure to get highlighted your desired dates.

📚 Note:

All the operations of this article are accomplished by using Microsoft Office 365 application.


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.

Launching New Formatting Rule dialog box to apply conditional formatting dates older than today

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

Choosing Format only cells that contain option

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

=TODAY()

  • Next, click the Format button.

Setting the suitable criteria

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

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

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.

Opening Conditional Formatting dialog box to apply conditional formatting

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

Use a formula to determine which cells to format to apply conditional formatting dates older than today

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

=C4>TODAY()-10

  • Afterward, click the Format button.

Writing suitable formula to apply conditional formatting dates older than today

  • 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

Apply Conditional Formatting to Highlight Dates Older Than Today Using Range

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.

Opening New Formatting Rule dialog box to apply conditional formatting dates older than today

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

Selecting Format only cells that contain option to apply conditional formatting

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

=TODAY()

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

=TODAY()-30

  • Afterward, click the Format button.

Applying formula using TODAY function in the empty box to apply conditional formatting

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

Highlight Dates by Conditional Formatting between Today and 30 Days Ago (Range of Dates)

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.

Choosing Between option to apply conditional formatting to highlight older dates within 90 days from today

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

=TODAY()

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

=TODAY()-90

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

Utilizing TODAY function to apply conditional formatting to highlight older dates within 90 days from today

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

Apply Conditional Formatting for Dates within 90 Days from Today

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.

Choosing Between option to apply conditional formatting to highlight older dates

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

=TODAY()

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

=TODAY()-180

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

Using TODAY function to apply conditional formatting to highlight older dates within 180 days or 6 months from today

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

Perform Conditional Formatting for Dates Within 6 Months from Today

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.

Choosing Between option to apply conditional formatting to highlight older dates

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

=TODAY()

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

=TODAY()-365

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

Using TODAY function to apply conditional formatting to highlight older dates within 365 days or 1 year from today

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

Use Conditional Formatting for Dates Within 1 Year

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

<< Go Back to Conditional Formatting Based on Date | Conditional Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

4 Comments
  1. Very clear instructions. How to make it so the blanks don’t get filled in?

    • Reply Avatar photo
      Osman Goni Ridwan Aug 21, 2022 at 10:49 AM

      Hello, JACK! For this, you have to add a new conditional Formatting rule. Go to Home tab >> Conditional Formatting >> New Rule.

      Then, Select the “Format Only Cells That Contain” and select the “Blanks” option in the “Format only cells with” box.

      Then, go to the “Format” option and select White as the fill color.

      Try this and let us know the outcome. Thank you!

  2. Is there a way to apply this rule so that the entire row is highlighted instead of just the cell?

    • Hello Noelle,

      To highlight the entire row, insert the following formula: =$C5 < TODAY()

      Formula

      In applies to select the rows
      Applies to

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo