Hello I have a Spreadsheet with a set of people and for each of them columns with formulas that bring the date a visit should occur. That formula is a number of days after the first visit (over a period of years. Usually a visit every 4 months). I have to make sure that visit occurs withing +/-10 days. I want to have the cell be of a particular colour if the date projected is still more than 10 days aways, another color if it is between -10 and the expected visit date, to turn reddish if it is one to ten days pass, and strong red if it is more than 10 days overdue.
I tried setting up some rules but with no success.
R2 =SI($I2<>"";$I2+1016;"") where I2 is the date of the initial visit and Week 144 occurs 1016 days after (04/10/2023) then I want this to be light salmon colour. If this was 25/11/2022 I want it let´s say orange.
If it was one day after (or up to ten days after), for example 15/11/2022 (yesterday being the ideal visit date) I want it light red
and if it was over ten days pass, let´s say 05/11/2022, strong red.