conditional formatting based on multiple criteria

[Calvest1]

Hi Experts,

I have some data stored in a table (Data Table).

Data Table

Date	Start Time	Duration	End Time	Task	Worker
24/07/2024​	06:00​	120​	08:00​	Test description	Worker 1
25/07/2024​	07:00​	120​	09:00​	Sleep	Worker 2

I would like to apply conditional formatting based on Date, Start / end time and worker in the cells below (Range to colour) in the example data the x would be coloured.

Range to colour

24/07/2024​	06:00​	07:00​	08:00​	09:00​	10:00​	11:00​	12:00​
Worker 1	X	X
Worker 2		X	X

Please can you help with a formula for this?

Thank you in advance

 

[Calvest1]

Ah, I see. This complicates things. I suppose I could have figured there would be more than one entry per worker per day, even though your sample data did not show it. Alright, I will take a look and see if I can work it out.

thank you - and sorry , i appreciate the help, i have been trying to do this for a week now and you have got further than i have.

Regards

 

[dreid1011]

What is the maximum possible time periods can a worker have in a day? And are multiple rows per worker per day an option?

 

[Calvest1]

What is the maximum possible time periods can a worker have in a day? And are multiple rows per worker per day an option?

It would be 8 hours per day, i have posted an image of the end result (note: i have got the text to work - just not the colour[colours do not need to be different])

do you mean multiple rows in the coloured area? - if so ideally not.

Thanks

 

[dreid1011]

Okay, I thought as much, but I was hoping otherwise. At the moment then, this is all I have, as the logic to get it nice and concise and more user friendly is beyond my grasp.

It's a spill formula, so you would need enough rows per worker in the colored section per day, and it's not convenient enough to just drag down, so you would have to update it for the first instance of each worker per day. I do hope someone else watching can help further refine it or provide a better solution, but here it as anyway just in case. Also need to drag it across the columns per worker.

Book1 7-25-2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Date	Start Time	Duration	End Time	Task	Worker			24/07/2024	6:00	7:00	8:00	9:00	10:00	11:00	12:00
2	24/07/2024	6:00	120	8:00	Test description	Worker 1			Worker 1	X	X
3	25/07/2024	7:00	120	9:00	Sleep	Worker 2			Worker 1					X
4	24/07/2024	10:00	60	11:00	Test Item 2	Worker 1			Worker 2
5
6									25/07/2024	6:00	7:00	8:00	9:00	10:00	11:00	12:00
7									Worker 1
8									Worker 2		X	X
Sheet1

Cell Formulas
Range	Formula
J2:P4	J2	=IFERROR(IF(BYROW(CHOOSECOLS(FILTER($B$2:$D$4,($A$2:$A$4=$I$1)*($F$2:$F$4=$I2),""),1,3),LAMBDA(r,AND(J$1>=CHOOSECOLS(r,1),K$1<=CHOOSECOLS(r,2),K$1<>""))),"X",""),"")
J7:P8	J7	=IFERROR(IF(BYROW(CHOOSECOLS(FILTER($B$2:$D$4,($A$2:$A$4=$I$6)*($F$2:$F$4=$I7),""),1,3),LAMBDA(r,AND(J$1>=CHOOSECOLS(r,1),K$1<=CHOOSECOLS(r,2),K$1<>""))),"X",""),"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J7:P8	Expression	=J7="X"	text	NO
J2:P4	Expression	=J2="X"	text	NO