Formatting dates 4 years from another date in a cell

[Purpleturtle]

Sorry if this is a dumb one. But let’s say I have a date in one cell past/future dates how do I format that to turn the row red after 4 years has passed from that date?

 

[etaf]

Edit - this is for a date 4 years old from todays date
can you give an example of what you mean exactly

after 4 years has passed from that date?

maybe misunderstanding

lets say its in cell a1
conditional format
and use formula
=A1<=DATE(YEAR(TODAY())-4,MONTH(TODAY()),DAY(TODAY()))

this will highlight any dates that are 4 years or older based on the date today

30, 60, 90-ETAF.xlsx
	A
1	5/21/18
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	Expression	=A1<=DATE(YEAR(TODAY())-4,MONTH(TODAY()),DAY(TODAY()))	text	NO

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A1 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=A1<=DATE(YEAR(TODAY())-4,MONTH(TODAY()),DAY(TODAY()))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[Purpleturtle]

No sorry, not based on todays date. Let’s say a cell is 23/05/2022 I want it to highlight when it reaches 23/05/2026. 4 years on from the original date

 

[etaf]

so the cell is changing value, in someway ?

A1 = 23/05/2022
then changes somehow , and when it reaches 23/05/2026 - its highlighted

for that you will need VBA, or a method of recording the original date in a different cell , so you can compare
the original date , to the date entered into the cell now

sorry i maybe completely misunderstanding -

i used today() - so when the computer date is 23/05/2026 - it will highlight, which will be in 4 years real time

so if i type in A1 23/05/2022
and then a few minutes later i type over that cell with 23/05/2026 - it highlights

 

[Purpleturtle]

I think I miss understood what you said! I’ll give it a try this morning thank you very much!

 

[etaf]

sorry we seem to be misunderstanding each other

I’ll give it a try this morning thank you very much!

let us know how you get on
I'm away for a few days from about lunchtime today UK timezone, - so apologies if i dont reply quickly

it would also be worth updating your profile with the version of excel you are using, so many new functions now on later versions, that a solution may use a function thats not available on your version of excel , if using an OLD version.
Unless i see otherwise , I use the latest version of 365 (Mac OSX, even the windows platform versions, now have different functions, not available on OSX)