Conditional Formatting Based on Date Older Than 1 Year in Excel

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we need to determine dates that are older than 1 year for our calculation. We can easily do it in Excel. In this article, I will show you the methods in Excel for conditional formatting based on date older than 1 year in Excel. There are 3 methods in this article.

Download Practice Workbook

Download this workbook and practice while going through the article.

3 Easy Methods for Conditional Formatting Based on Date Older Than 1 Year in Excel

This is the dataset for today’s article. We have some products along with their Order Number and Order Date. I will find out the dates that are older than 1 year using conditional formatting.

I have mentioned today’s date just for convenience. This will not have any effect on the process.

1. Use of Highlight Cells Option

First of all, I will show how to use the Highlight Cells Option to find out the dates older than 1 year. In this method, I will use the TODAY function.

Steps:

• First, select the range D5:D11.
• Then, go to the Home
• After that, go to Conditional Formatting.
• Then, select Highlight Cells Rules.
• Finally, choose Less Than…

• A box will appear. Write down the following formula in the box

• Then, press OK.

• Excel will find out the dates older than today.

Read More: Conditional Formatting for Dates Older Than Certain Date in Excel

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)
• If Cell Contains Date Then Return Value in Excel (5 Examples)
• Excel Formula If One Date is Greater Than Another Date

2. Set New Rule Based on Date Older Than 1 Year

You can also find out the dates older than 1 year by setting a new rule in conditional formatting. Let me show you how to do so step by step.

Steps:

• First of all, select the range D5:D11.
• Then, go to the Home
• After that, select Conditional Formatting.
• Finally, select New Rule.

• A box will appear. Select Format only cells that contain.

• Then, choose less than from the drop-down list.
• After that, write down the following formula

• Then, you need to format the cells that have dates older than 1 year. For this, select Format.

• Format the way you want. I will change the Fill color.
• Click OK.

• Then, click OK.

• Excel will format dates older than 1 year.

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

3. Apply New Formula to Apply Conditional Formatting

The next method is applying a new formula in conditional formatting. Let’s do it step by step.

Steps:

• First of all, select the range D5:D11.
• Then, go to the Home
• After that, select Conditional Formatting.
• Finally, select New Rule.

• A box will appear. Select Use a formula to determine which cells to format.
• Then, write down the formula

• Then, click OK.

• Excel will do the formatting. The output will be like this.

Read More: Excel Formula If Date Is Greater Than 2 Years (3 Examples)

Things to Remember

• The day when you use the TODAY function will be considered today in Excel.

Conclusion

In this article, I have shown 3 methods in Excel for conditional formatting based on date older than 1 year in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.

Related Articles

• Check If Date Is 7 Days of Another Date in Excel (7 Easy Ways)
• Use COUNTIF for Date Greater Than 30 Days in Excel
• How to Compare Dates in Two Columns in Excel (8 Methods)
• Excel Formula If Date Is Greater Than 365 Days (4 Ideal Examples)
• Compare Dates to Today with Excel VBA (3 Easy Ways)
• How to Compare If Date Is Before Another Date in Excel