Conditional formatting of Cells based on date less a countdown.

[KyleG]

Hi,

In the below image i have coloured the cells based on the current date.
What i want is for the sheet to colour code as appropriate when opened.
So Green if the cell is marked DONE
Red if today is >than the COO Date less the T-minus date (first three cells would go red if today is greater than the 5/8/2024, A8 starts going red if today is greater than 08/08/2024. eg. needs action
orange if today = the COO Date less the T-minus date is today.
No colouration if the above are not applicable

Happy if i have to populate the A column in full eg. multiple T-14s in A5, A6, and A7

 

[KyleG]

So perhaps you could give a small set of sample data that had some "Time minus" cells and some "Time plus" cells and show/explain the expected results?

I have marked in yellow what should actually be red based on how the rest of the sheet acts. The A column represents the date in relation to the COO date in Row 4. So E37 the yellow should have gone red from the 19/08/2024. My original brief did not include T+ values but otherwise works perfectly.

Current Rules are:

=SEARCH("DONE",E6) GREEN
=LET(L,LOOKUP("ZZ",$A$6:$A6),AND(TODAY()>E$4+MID(L,FIND("-",L),99))) RED
=LET(L,LOOKUP("ZZ",$A$6:$A6),AND(TODAY()=E$4+MID(L,FIND("-",L),99))) ORANGE

 

[Peter_SSs]

If the prefix in column A is only ever a single character then change the red formula to

=AND(TODAY()>E$4+MID(LOOKUP("ZZ",$A$6:$A6),2,99))

If the col A prefix could be more than one character then for red try

=LET(L,LOOKUP("ZZ",$A$6:$A6),AND(TODAY()>E$4+MID(L,IFERROR(FIND("-",L),FIND("+",L)),99)))

If either of the above suits then I guess you will need a similar change for the orange formula.