conditional formattingformulasgoogle sheetsgoogle-sheets-dates
If possible, I would like to have a color schema applied to the table.
For instance, the cell that has the closest date to today will be green
The cell that has the furthest date to today will be red.
Note the date in the cells automatically changes and it's not static.
There might be a better way, but try three conditional format rules on C1 in this order:
"Custom formula is"; Value: =IF(A1="Done",TRUE,FALSE): set background to White
"Date is after"; "exact date..."; Value: =NOW()+3: set background to Green
"Cell is not empty": set background to Red. This is the default if the cell is not "Done" and the date is not after three days from now.
You could also keep rule 1, and then have rule 2 being a Colour Scale. You might need to put an extra cell to convert NOW() into a day count, though: =NOW()-3 for example.
=G1<TODAY(). Note that this says "G1" because the formula is stated as it applies to the upper left corner of the range. It will be automatically adjusted for other cells in the range according to the usual rules for relative references. =G1<TODAY()+90=G1>=TODAY()+90If several rules apply, those listed later override the earlier ones.
The result:
Best Answer
red color:
=IF(MIN($B$2:$B)=$B2, 1)green color:
=IF(MAX($B$2:$B)=$B2, 1)