How to change conditional formatting with formulas concerning dates.

[craig.norris]

In my workbook I have several column with dates, these are benchmarks that I need to follow and have my patient's follow. They concern the dates of treatments and of lab work. My last column is Contact Needed and does not contain any formulas or code. Is there a way to change the color of the row to adjust for how close we are to the treatment date or the lab draw date.

I was thinking if I were +/- 7 days from each blood draw the row would be yellow, so I could contact the patient and remind them to get the labs done. If it was over 7 days past scheduled blood draw, the row would be light red, as the urgency to get labs and continue treatment has increased as they are past due. In like manner, if treatment are within 14 days, the row would be yellow so i could call them and set the appointment. If past the treatment date, the row goes red and I have 14 days to get them in or we have to start treatment s all over again. Here is the beast. Thanks for any help on this.

Book0.5 template.xlsx

[FDibbins]

Which date do you want to base this on?

Also, a slightly shorter version of this...
=IF(A4=0,"",IF(K4="q 3 mths",L4+90,IF(K4="q 4 mths",L4+120,IF(K4="q 5 mths",L4+150,IF(I4=0,"Needs Labs","Tx Failure")))))
=IF(A4=0,"",IF(I4=0,"Needs Labs",IF(K4<>"",L4+(30*MID(K4,3,1)),"Tx Failure")))

[craig.norris]

I am sorry it has been so long to reply.
I am basing the first set of color changes on the date of "next blood draw" and the second set of color changes on "next treatment"

And thanks for the shorter version.

[FDibbins]

I put =TODAY() in A1, to use as today's date

You are already changing the row color with a CF rule, so maybe just change the cell color in col O?

This is for changing the cell yellow if the date gets within 7 days from today...
=$O4<=$A$1-7
I highlighted O2:O30 (adjust as needed) for this. See if this is giving you what you want (so far) and then see if you can add a new rule, based on thatm for red? (shout if you need help )