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.


Download Practice Workbook

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


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

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.

We will figure out those employees who joined before August 29, 2022, or those dates that are older than August 29, 2022.


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.

Launching Conditional Formatting Dialog Box to Apply New Rules to Format Cells

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

=C5<DATE(2022,8,29)

  • Next, click the Format option.

Writing Proper Formula Using The DATE Function to Get the Formatting

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

Using DATE Function to Apply Conditional Formatting for Dates Older Than A Certain Date

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

Read More: Conditional Formatting Based on Date Older Than 1 Year in Excel


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.

Opening Conditional Formatting Dialog Box to Apply New Rules to Format Cells

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

=C5<TODAY()

  • Click the Format option.

Writing Suitable Formula Using The TODAY Function

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

Applying TODAY Function to Apply Conditional Formatting for Dates Older Than A Certain Date

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

Read More: How to Compare Dates in Two Columns in Excel (8 Methods)


Similar Readings


3. Utilizing DATEVALUE Function

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

📌 Steps:

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

Launching Conditional Formatting Dialog Box to Format Dates

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

=C5<DATEVALUE("08/29/22")

  • Then, click the Format option.

Writing Suitable Formula Using The DATEVALUE Function to Get the Formatted Date Cells

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

Utilizing DATEVALUE Function to Apply Conditional Formatting for Dates Older Than A Certain Date

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

Read More: Excel Formula If One Date is Greater Than Another Date


4. Using 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.

Input Desired Date to Format the Older Dates with Respect to it

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

Changing the Number Format to Formatting the Older Dates

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

Showing Date Value in General Format to Input in Conditional Formatting Rule Box

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

Opening Conditional Formatting Dialog Box to Format Dates

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

=44802

  • Next, click the Format option.

Setting Features According to Our Desire to Format Older Date Cells

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

Using Simple Method to Highlight to Apply Conditional Formatting for Dates Older Than A Certain Date

 

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

Read More: Excel Conditional Formatting for Date Within 3 Months (3 Methods)


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.

Inputting Desired Date

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

Launch New Rules for Conditional Formatting Dialog Box to Format Older Dates

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

=C5<$E$5

  • Click the Format option.

Defining Customize Rule in the Dialog Box to Format Older Dates with Respect to a Certain Date

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

Customizing New Rules with Defined Cell Reference to Apply Conditional Formatting for Dates Older Than A Certain Date

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.

Read More: Excel Formula If Date Is Greater Than 365 Days (4 Ideal Examples)


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.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo