How to set up conditional formatting, the date is more than a few days?

Question

excel

Please help me set up conditional formatting. I show a piece of the table:


I change D2 and this affects all cells of column E:


Is it possible by changing the parameter in DAY ():


How to ensure that a change in one cell in column D does not affect the entire column E, but only the neighboring cell?

Answer 1

It is probably necessary to remove absolute references
=$i$11+day($d2)
Although it is not clear why the day() function is taken from a number at all
And if you need to take a value from the same line, then you need to set 1
For example:
A1 is the current cell
B1 is a cell to the right of the current one [BA=+1 column], but in the same row[1-1=0 rows]
C2 - cell to the right of the current one 2 columns [CA=+2 columns], in the row below[2-1=+ 1 row]
$B1 - cell of column B(fixed) but in the same row[1-1=0 rows]
$D$1 - cell $D$1(fixed)
Or a universal variant:
indirect("R"&(ROW( )+2)&"C"&(COLUMN()+3);0)- cell shifted by 2 rows and 3 columns from the current one to the right and down
indirect("R"&(ROW()-2)&"C"&(COLUMN()-3);0) - cell shifted by 2 rows and 3 columns from the current one to the left and up