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]

Also should note the number of stores numbers could be up to 15 so 30 across roughly.

 

[myall_blues]

Try this. Copy to other columns as required. You will need to fill in column A.

Book1
	A	B	C	D
1
2
3
4	COO Date		19/08/2024
5	T-14		DONE
6	T-14
7	T-14
8	T-11
9	T-11
10	T-10
11	T-10
12	T-7
13	T-7
14	T-7
15	T-7
16
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:C15	Expression	=C5="DONE"	text	YES
C5:C15	Expression	=TODAY()>C$4-VALUE(MID($A5,FIND("-",$A5)+1,99))	text	NO
C5:C15	Expression	=TODAY()=C$4-VALUE(MID($A5,FIND("-",$A5)+1,99))	text	NO

 

[Peter_SSs]

Another option that enables you to ..

1. Apply the Conditional Formatting to a single area instead of copying column to column, and
2. Leave the blank cells in column A rather than filling in the T values every row.

In my example below I selected D5:H15 and applied the CF shown.

24 08 09.xlsm
	A	B	C	D	E	F	G	H
1			Store		Store 2		Store 3
2
3
4	COO Date		19-Aug-24		26-Aug-24		02-Sep-24
5	T-14			DONE
6
7						DONE
8	T-11
9
10	T-10							DONE
11								DONE
12	T-44
13	T-7
14
15	T-0					DONE		DONE
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:H15	Expression	=D5="DONE"	text	NO
D5:H15	Expression	=LET(L,LOOKUP("ZZ",$A$5:$A5),AND(D$1="",TODAY()>C$4+MID(L,FIND("-",L),99)))	text	NO
D5:H15	Expression	=LET(L,LOOKUP("ZZ",$A$5:$A5),AND(D$1="",TODAY()=C$4+MID(L,FIND("-",L),99)))	text	NO

If you apply the CF and it does not appear to be working correctly go to Conditional Formatting -> Manage rules ... and make sure the rules appear in the order shown below.
The order of the rules can be changed using the circled arrows.

 

[KyleG]

Thanks to you both. I have actioned as per Peter_SSs's response and it works great.
I want to make one change to the "Done" criteria. i want it to go green if it contains "Done". We have two scenarios when Done will also include additional information (name, text of a time). i cant figure out to get a wildcard character to be included.

 

[Peter_SSs]

Change the 'DONE' CF formula to

=SEARCH("DONE",D5)

That is unless those columns in between could possibly also include a word containing the letters "done" (eg abandoned).
If that was possible then to stop them also going green use this for the CF formula for green

=AND(SEARCH("DONE",D5),D$1="")

 

[KyleG]

That works. Thanks Peter.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[KyleG]

Good Morning,
Two things have come up with the red/orange formula.
1. When the Time minus cell is actually a Time plus cell the formula doesnt apply.
2. And me trying to understand what is happening in the formula as opposed to just taking your formula and applying it.

Could i please get assistance tweaking this for the T+ situations and if time allows walk me thru what the formula does.
As near as i can figure the formula more or less says look up a text (i assume thats what "ZZ" is saying) in the A cell and give it the name "L".
determine if it meets the > or = date find the - in the A column and return the aforementioned named "L" number, applying the formula and determining the colour of the cell based on the date.
I think the 99 is just a place holder number for the number of characters in the cell, it could equally be 20 or 200 as long as its not less than the number of characters in the cell.

I have not been able to determine what the "D$1" part of the formula is achieving. Where i THINK its not working for positive T values is the Find function part. I THINK i need some sort of OR argument but have not been able to string together anything that works.

your help as always is much appreciated.

 

[Peter_SSs]

When the Time minus cell is actually a Time plus cell the formula doesnt apply.

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?