Can you use formulas in conditional formatting or do I think diff.

K

KenRamoska

I have a spreadsheet with names and dates. They want a date to turn
color or do something when the date hits 3 years old. So if I did something
on 1.1.04 they want it to indicate expired when 1.1.07 hits.
Any suggestions would be greatly appreciated.
Thanks!!!!
 
W

willwonka

Sure... Using Conditional Formatting, assuming date is in A1:

Format... Conditional Formatting...
Hit Drop down to say Formula is
=if(now()-a1>1095,1,0)
Click on Format to change to desired result.
That should do it.
 
P

Pete_UK

There could be a leap year within that 3-year span (generally), so
maybe better to make it:

=if(now()-a1>1096,1,0)

Hope this helps.

Pete
 
K

KenRamoska

AWESOME!!!!!!!!!!!!!!

willwonka said:
Sure... Using Conditional Formatting, assuming date is in A1:

Format... Conditional Formatting...
Hit Drop down to say Formula is
=if(now()-a1>1095,1,0)
Click on Format to change to desired result.
That should do it.
Click to expand...
 
C

CLR

........ alternative method is to create a RangeName given a value of
=EDATE(TODAY(),-36)

........then do a CF of "CellValueIs", "less than or equal to",
=YourRangeName

Vaya con Dios,
Chuck, CABGx3
 
Top