Table of Contents
Excel is one of the most powerful and versatile data analysis tools. It is used in a wide variety of industries and professions to make data organized and easily accessible. The incredible features of Excel allow users to quickly and accurately analyze data, create presentations, and even automate certain tasks. Excel’s conditional formatting tools are one of the most popular and powerful features of the program. Conditional formatting allows users to apply specific formatting to cells that meet certain criteria. In this article, we will discuss how to apply conditional formatting to dates older than one year. We will discuss how to set up the conditional formatting and how to use it to quickly identify dates older than one year.
To apply conditional formatting to cells that have a date older than one year from the current date in Excel, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.
The following example shows how to use this option in practice.
Example: Apply Conditional Formatting to Dates Older Than 1 Year
Suppose we have the following dataset in Excel that shows the date that various people most recently visited a gym:
This article is being written on 8/18/2023.
Suppose we would like to apply conditional formatting to any date that is over a year old.
To do so, we can highlight the cells in the range B2:B13, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:
In the new window that appears, click Use a formula to determine which cells to format, then type =B2<TODAY()-365 in the box, then click the Format button and choose a fill color to use.
Once we press OK, all of the cells in the range B2:B13 that have a date over a year old from today (8/18/2023) will be highlighted:
If you would like to apply conditional formatting to dates that are a different number of years older than today, simply multiply the last value in the formula by that number.
For example, you could type =B2<TODAY()-365*2 in the conditional formatting rule box to instead highlight cells that have a date older than 2 years from today.
Note: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.
In conclusion, conditional formatting in Excel can be used to highlight any cells in a range that have a date older than a specific number of years. This can be useful for quickly identifying values that may be out of date or irrelevant. To do this, you can use the New Rule option under the Conditional Formatting menu in the Home tab. This article provided an example of how to use this option in practice.