Conditionally format if date is same/greater or smaller than current date

Question

I'm trying to get my Google sheet to automatically colour a cell red if the current date is the same or greater than the date in the cell.

Example: today is 21/02/2020.

The date in the cell is 25/02/2020. The cell is green.

The date in the cell is 21/02/2020. The cell is red.

The date in the cell is 15/02/2020. The cell is red.

How can I achieve this?

The date in the cell will be changing pretty often. It's to indicate when an update is due. When the update has been completed, the date in that cell will change. This will of course be done manually. I'm just looking to find the best way of doing this.

marikamitsos · Answer

Under Custom formula is and for the same range (in our example S1:S), you should use the following 2 formulas.
(For reference only the current date 2/22/2020 is shown in cell R1),

For dates greater than the current date. (green)

=IF(LEN(S1)>0,IF(S1>DATEVALUE(TODAY()),TRUE,FALSE),"")

For dates that are the same or smaller than the current date. (red)

=IF(LEN(S1)>0,IF(S1<=DATEVALUE(TODAY()),TRUE,FALSE),"")

Pro Tip: In Conditional formatting the order of the rules is very important, because if they happen to overlap the top ones cancel the lower ones. But not in this case. The way the formulas are structured they never overlap.

pjmg · Answer

Color everything in red then go to Format > Conditionnal Formatting

Choose Format cell if

Date is after >

Exact date  >

Cell reference

Conditionally format if date is same/greater or smaller than current date

2 Answers

Add your own answers!

Ask a Question