Soumik Dutta
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.
- 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.
- 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.
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.
- 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.
- 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.
Read More: How to Compare Dates in Two Columns in Excel (8 Methods)
Similar Readings
- Excel Formula If Date Is Less Than Today (4 Examples)
- How to Find If Date Is Within 3 Months in Excel (5 Easy Ways)
- Excel Formula If Date Is Greater Than 2 Years (3 Examples)
- If Cell Contains Date Then Return Value in Excel (5 Examples)
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.
- 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.
- 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.
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.
- 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.
=44802
- 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.
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.
- 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:
=C5<$E$5
- 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.
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!
