This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Is it possible to select records that are more than a year out of date?
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Using EDATE function as per my previous post is a better option than subtracting 365 as per V. Arya's post.
EDATE automatically takes care of leap years so if your project continues past 29 Feb 2020 then the leap year needs to be considered.
Use the following formula to determine which dates are more than a year old from today. Note that A2 is the first cell of the range being conditionally formatted and Excel will apply the correct formula to the remainder of the "Applies to" range
=A2<EDATE(TODAY(),-12)
Refer to the screen shot below where I have applied conditional formatting to the range A2:A17 with the Conditional Formatting dialog.
Note: Select option "Use a formula to determine which cells to format"
Hello Helen
I am V. Arya, Independent Advisor, to work with you on this issue. CF can process all dates in past and future (as per limits of Excel dates).
So, you can do following to create a CF as an illustration
Make a selection of your range say C2:C10
Select CF in Home tab
Click New Rule followed by Use a formula to determine which cells to format and put following formula
Click Format button and choose a color say Green
Use following formula and OK
=C2<TODAY()-365