Conditional Formatting for Dates Older Than Certain Date in Excel

In our Excel spreadsheet, we use dates to track the data according to our desire. Sometimes, we require conditional formatting to highlight those dates on specific criteria. In the article, we will demonstrate five easy ways to apply conditional formatting for dates older than a certain date in Excel. If you are also curious about it, download our practice workbook and follow us.

How to Apply Conditional Formatting for Dates That Are Older Than a Certain Date in Excel: 5 Easy Methods

To demonstrate the approaches, we consider a dataset of 10 employees’ joining dates of any new startup company. So, our dataset is in the range of cells B5:C14.

1. Using DATE Function

In this method, we will use the DATE function to apply conditional formatting for dates older than a certain date. The steps of this approach are given below:

📌 Steps:

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

• As a result, a small dialog box called New Formatting Rule will appear on your device.
• Then, select the Use a formula to determine which cells to format option.
• After that, in the empty box below the text, Formula values where this formula is true, write down the following formula:

• Next, click the Format option.

• Another dialog box titled Format Cells will appear.
• Now, set the cell formatting according to your desire. We choose the Orange, Accent 2, Lighter 40% color from the Fill tab.
• Finally, click OK to close the Format Cells dialog box.

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

• You will see that those dates that are older than August 29, 2022 show highlighted.

Thus, we can say that our formula works perfectly, and we are able to apply conditional formatting for dates older than a certain date.

2. Applying TODAY Function

In this approach, we are going to use the TODAY function to apply conditional formatting for dates older than a certain date. The steps of this process are given as follows:

📌 Steps:

• First, select the range of cells C5:C14.
• After that, in the Home tab, click on the drop-down arrow of the Conditional Formatting > New Rules option located in the Styles group.

• As a result, a small dialog box entitled New Formatting Rule will appear on your device.
• Now, select the Use a formula to determine which cells to format option.
• Then, in the empty box below the text, Formula values where this formula is true, write down the following formula:

• Click the Format option.

• As a result, another dialog box called Format Cells will appear.
• Next, set the cell formatting according to your desire. For our case, we choose the Orange, Accent 2, Lighter 40% color from the Fill tab.
• Finally, click OK to close the Format Cells dialog box.

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

• You will notice that those dates that are older than August 29, 2022 show highlighted.

So, we can say that our formula works effectively, and we are able to apply conditional formatting for dates older than a certain date.

3. Utilizing DATEVALUE Function

In the following process, the DATEVALUE function will help us apply conditional formatting for dates older than a certain date. The procedure of this process is shown below step-by-step:

📌 Steps:

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

• A small dialog box entitled New Formatting Rule will appear.
• Afterward, select the Use a formula to determine which cells to format option.
• Next, in the empty box below the text, Formula values where this formula is true, write down the following formula:

• Then, click the Format option.

• Another dialog box called Format Cells will appear.
• Now, set the cell formatting according to your desire. For our cells, we choose the Orange, Accent 2, Lighter 40% color from the Fill tab.
• At last, click OK to close the Format Cells dialog box.

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

• You will get those dates that are older than August 29, 2022 show highlighted.

Hence, we can say that our formula works precisely, and we are able to apply conditional formatting for dates older than a certain date.

4. Using a Simple Method to Highlight

In this case, we are going to use a simple method to apply conditional formatting for dates older than a certain date. The procedure of this method is explained below step-by-step:

📌 Steps:

• Firstly, in cell D5, write down your desired date. We write down August 29, 2022.

• Now, change the number format of that cell from Date to General to get the date value from the Number group.

• You will get the date value in General format, which is 44802.

• Then, select the range of cells C5:C14.
• In the Home tab, click on the drop-down arrow of the Conditional Formatting > New Rules option located in the Styles group.

• As a result, a small dialog box entitled New Formatting Rule will appear on your device.
• After that, select the option called Format only cells that contain.
• Now, you will see some fields where we have to set the features according to our desire.
• In the first field, we set the Cell Value.
• Then, in the second empty field, we set the option titled less than.
• Finally, in the last field box, write down the date value.

• Next, click the Format option.

• Another dialog box called Format Cells will appear.
• Now, set the cell formatting according to your desire. For our cells, we choose the Orange, Accent 2, Lighter 40% color from the Fill tab.
• Finally, click OK to close the Format Cells dialog box.

• At last, click the OK button.

• You will see that those dates that are older than August 29, 2022 show highlighted.

Therefore, we can say that our method works successfully, and we are able to apply conditional formatting for dates older than a certain date.

5. Customizing New Rules with Defined Cell Reference

In the following approach, we will customize the New Rules option to apply conditional formatting for dates older than a certain date. The steps of this process are described below:

📌 Steps:

• At the beginning of this process, in cell E5, write down your desired date. We write down August 29, 2022.

• After that, select the range of cells C5:C14.
• Then, in the Home tab, click on the drop-down arrow of the Conditional Formatting > New Rules option located in the Styles group.

• A small dialog box called New Formatting Rule will appear.
• Next, select the Use a formula to determine which cells to format option.
• In the empty box below the text, Formula values where this formula is true, write down the following formula:

• Click the Format option.

• As a result, another dialog box called Format Cells will appear.
• Set the cell formatting according to your desire. For our cells, we choose the Orange, Accent 2, Lighter 40% color from the Fill tab.
• Finally, click OK to close the Format Cells dialog box.

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

• You will notice that those dates that are older than August 29, 2022 show highlighted.

In the end, we can say that our approach works perfectly, and we are able to apply conditional formatting for dates older than a certain date.

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 for dates older than a certain date in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations. Keep learning new methods and keep growing!

<< Go Back to Dates | Compare | Learn Excel