Using datedif to format cell color

[Neal Ostrander]

How can I set the color of a cell based on the difference between the date
entered and todays date. For example if the date entered is equal to 90 days
but greater than 60 days away from todays date I want to color the cell
green.
Thanks in advance for any help.
Neal

 

[Sheeloo]

If your date is in D8 then use
=IF(AND(TODAY()-D8<91),(TODAY()-D8>60)) for conditional formatting

 

[Neal Ostrander]

I have created the following three conditial formats:
=IF(AND(TODAY()-$J$2<=30),(TODAY()-$J$2>0)) highlights Red
=IF(AND(TODAY()-$J$2<=60),(TODAY()-$J$2>=31)) highlights Yellow
=IF(AND(TODAY()-$J$2<=90),(TODAY()-$J$2>=61)) highlights Green
in the applies to field of the dialog box I have :
=$J$2:$J$10000
When I set a date that satifies one of the conditions it changes the color
of the entire column. I am obviously doing something wrong here. What I am
trying to accomplish is if the date in a cell in column J is less than 90
days from todays date change cell to green, 60 days to yellow and 30 days to
red. How do I go about applying the conditional formating to the whole column
leaving blank cells with no fill.
Thanks
Neal

 

[Sheeloo]

Neal,

You are using absolute address hence value in J2 is being checked for all
the cells in the range. If you have a $ before col letter or row no. it
remains unchanged when you apply the formula to other cells ..

To see how it works enter =A1 in B1 and copy it down and see the formula in
B2, B3 etc.
enter $A$1 in D1 and copy down and compare with the above

other variations are $A1 and A$1


Change $J$2 to J2 and it will work...