Conditional formatting based upon today, along with days before and after. (Google Sheets)

[mtaylor]

Hi, thanks for looking.

I have a spreadsheet which is used to record covid results in a school. I have a formula entered which calculates the 'valid until' date, which would be +14 days from test date (=IF(ISBLANK(E3),"",E3+14).

What I would like is that cell (F3:F58 in this case to:

Turn green if the date is not within 2 days of today
Turn orange if the date within 2 days of today (including today
Turn red if the date is past today.

I currently have this:

Which I believe works ok for the red but the others.

The empty cells (of which there is no date, currently also turn green, I'd prefer if they remain colourless.)

Again, thanks for looking.

Am in Google Sheets trying to smash this out.

 

[Saurabhj]

Hi, Use below:
For RED:
=AND($F3 < TODAY(),NOT(ISBLANK($F3)))

For ORANGE:
=AND($F3 >= TODAY(),$F3 <= TODAY()+2)

For GREEN
=$F3 > TODAY()+2

 

[mtaylor]

Hi, Use below:
For RED:
=AND($F3 < TODAY(),NOT(ISBLANK($F3)))

For ORANGE:
=AND($F3 >= TODAY(),$F3 <= TODAY()+2)

For GREEN
=$F3 > TODAY()+2

Love it - thanks a bunch

Can I ask about the blank cells, so for those that don't have a date in the E column, I don't want the F column cell equivalent to turn green.

Again, the main issue has been solved, this now is for aesthetic reasons.

Thanks again

 

[Saurabhj]

Hi, to handle blank I used code while showing RED cell. We can use same in GREEN if required.

=AND($E3 > TODAY()+2, NOT(ISBLANK($E3)))

 

[mtaylor]

Hi, to handle blank I used code while showing RED cell. We can use same in GREEN if required.

=AND($E3 > TODAY()+2, NOT(ISBLANK($E3)))

Jackpot!

Thank you very much!

 

[Saurabhj]

Thanks for the feedback.