Excel Conditional Formatting Dates Older than Today (3 Simple Ways)

Simple method to count and Highlight dates older than today

Sometimes we need to highlight or count dates under different conditions in MS Excel. For this purpose, Excel provides us with great approaches to use 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.

Download the Practice Workbook

From here you can download the workbook which was used to show all these methods. Additionally, with all the examples I have attached the Delivery Dates example in this workbook.

3 ways to do Conditional Formatting Dates Older than Today in Excel

1. Simple method to count and Highlight dates older than today

Let’s assume that we have a data set of warehouse products with their shipment dates. Now we will highlight the dates that are older than today.

Simple method to count and Highlight dates older than today

Step 1: Select the Date cells.

 Select the Date cells

Step 2: Then follow the steps:

  1. Go to the Home tab.
  2. Move to the Styles section.
  3. Select the Conditional Formatting option.
    Conditional Formatting Process

Step 3: Select New Rule.

 Select New Rule

Step 4: Select the Format only cells that contain options.

Select the Forma only cells that contain option

Step 5: For the marked drop-down, the menu changes it to less than as shown in the picture.

For the marked drop down menu change it to less than as shown in the picture.

Step 6: Enter the formula =TODAY() in the marked box.

Enter the formula =TODAY() in the marked box

Step 7: Click on the Format button.

 Click on Format button

Step 8: Then follow the process:

  1. Go to the Fill section and choose any color. (I have selected Red here)
  2. After that press Ok.
    Fill Formatting

Step 9: Click on the OK button.

Click on the OK button

Step 10: Then all the older dates will be highlighted in red color.

Then all the older dates will be highlighted with red color

So here we have highlighted all the shipment dates that are older than today’s date herewith red color using Conditional Formatting.

2. Conditional Formatting Dates Older than Today Using Range

In the previous method, I showed a simple method to do conditional formatting dates older than today. Now in this section, we will do the same thing with a range like the previous 20 or 15 days. So here I will show how to make conditional formatting dates older than 10 days from today. Let’s consider the same example shown in method 1 but here we will find out and highlight the shipment dates older than 10 days from today.

Step 1: Select the Date cells.

 Select the Date cells

Step 2: Go to the Conditional Formatting option like the previous one.

 Conditional Formatting Process

Step 3: Select New Rule.

Select New Rule

Step 4: Select Use a formula to determine which cells to format option.

Select Use a formula to determine which cells to format option.

Step 5: Enter the formula =C4<TODAY()-10 in the marked box.

Enter the formula in marked box

Step 6: Select the Format option.

Select the format option

Step 7: Go to the Fill option and select the color that you desire (here I have selected Red) then press OK.

Color selection process in format

Step 8: Then click on the Ok button.

Click on Ok button

Step 9: Then all the dates will be changed.

Highlighted dates after using range

In this way, we can easily highlight and focus on the dates that are ten or more days older than today.

3. Highlight Dates between Today and 30 Days Ago (Range of Dates)

Here we will highlight dates between today and 30 days ago. For showing this let’s consider the same example above.

Highlight Dates between Today and 30 Days Ago

Step 1: Do the same steps that are mentioned in method 1 up to Step 5. Then select between from the drop-down list.

Then select between from the drop down list

Step 2: Enter the formulas =TODAY() and =TODAY()-30 in the marked boxes.

Enter the formulas =TODAY() and =TODAY()-30 in the marked boxes

Step 3: Select the Format option and do formatting similar to Step 7, Step 8, Step 9 in method 1.  Then press the Ok button.

 then press the Ok button of Conditional formatting.

Step 4: Then all the dates between today and 30 days ago will be highlighted.

Then all the dates between today and 30 days ago will be highlighted

Conclusion

These are the three simple, yet distinct, ways to highlight and find out the older dates than today using Conditional Formatting. I hope these methods will help you to resolve this type of issue. Please inform us if you’ve any difficulties using these methods.

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo