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

 

[dreid1011]

Are the X's in your sample part of the actual data or are you just using them to show what you want highlighted?

 

[Calvest1]

Are the X's in your sample part of the actual data or are you just using them to show what you want highlighted?

Hi dreid1011,

It is just to show what i need highlighting.

Thank you

 

[dreid1011]

Will the two lists always be in the same order by Worker? If not, how will they be sorted?

Will the duration always be a multiple of 60? If not, what increments could it be in?

 

[Calvest1]

Will the two lists always be in the same order by Worker? If not, how will they be sorted?

Will the duration always be a multiple of 60? If not, what increments could it be in?

it will be sorted by worker, date, and time.

the duration could change to 15, 30, 45, 60 and up depending on how long is required.

Thanks

 

[dreid1011]

it will be sorted by worker, date, and time.

the duration could change to 15, 30, 45, 60 and up depending on how long is required.

Thanks

 

[dreid1011]

Okay, this is what I have. If columns I:P are an accurate representation of your daily layout, then you will need a separate CF formula for each day.

Book1
	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	06:00	07:00	08:00	09:00	10:00	11:00	12:00
2	24/07/2024	06:00	120	08:00	Test description	Worker 1			Worker 1	X	X
3	25/07/2024	07:00	120	09:00	Sleep	Worker 2			Worker 2
4
5									25/07/2024	06:00	07:00	08:00	09:00	10:00	11:00	12:00
6									Worker 1
7									Worker 2		X	X
Sheet1

Cell Formulas
Range	Formula
J2:P3	J2	=IF(AND(J$1>=FILTER($B$2:$B$3,($A$2:$A$3=$I$1)*($F$2:$F$3=$I2),""),K$1<=FILTER($D$2:$D$3,($A$2:$A$3=$I$1)*($F$2:$F$3=$I2),""),K$1<>""),"X","")
J6:P7	J6	=IF(AND(J$1>=FILTER($B$2:$B$3,($A$2:$A$3=$I$5)*($F$2:$F$3=$I6),""),K$1<=FILTER($D$2:$D$3,($A$2:$A$3=$I$5)*($F$2:$F$3=$I6),""),K$1<>""),"X","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J6:P7	Expression	=IF(AND(J$1>=FILTER($B$2:$B$3,($A$2:$A$3=$I$5)*($F$2:$F$3=$I6),""),K$1<=FILTER($D$2:$D$3,($A$2:$A$3=$I$5)*($F$2:$F$3=$I6),""),K$1<>""),"X","")="X"	text	NO
J2:P3	Expression	=IF(AND(J$1>=FILTER($B$2:$B$3,($A$2:$A$3=$I$1)*($F$2:$F$3=$I2),""),K$1<=FILTER($D$2:$D$3,($A$2:$A$3=$I$1)*($F$2:$F$3=$I2),""),K$1<>""),"X","")="X"	text	NO

 

[dreid1011]

You can ignore the cell formulas, those are not necessary. Those are there for me to work out the formula for the CF.

The CF formulas can even be shortened a little bit:

J2:P3

=AND(J$1>=FILTER($B$2:$B$3,($A$2:$A$3=$I$1)*($F$2:$F$3=$I2),""),K$1<=FILTER($D$2:$D$3,($A$2:$A$3=$I$1)*($F$2:$F$3=$I2),""),K$1<>"")

J6:P7

=AND(J$1>=FILTER($B$2:$B$3,($A$2:$A$3=$I$5)*($F$2:$F$3=$I6),""),K$1<=FILTER($D$2:$D$3,($A$2:$A$3=$I$5)*($F$2:$F$3=$I6),""),K$1<>"")

 

[Calvest1]

Okay, this is what I have. If columns I:P are an accurate representation of your daily layout, then you will need a separate CF formula for each day.

Book1
	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	06:00	07:00	08:00	09:00	10:00	11:00	12:00
2	24/07/2024	06:00	120	08:00	Test description	Worker 1			Worker 1	X	X
3	25/07/2024	07:00	120	09:00	Sleep	Worker 2			Worker 2
4
5									25/07/2024	06:00	07:00	08:00	09:00	10:00	11:00	12:00
6									Worker 1
7									Worker 2		X	X
Sheet1

Cell Formulas
Range	Formula
J2:P3	J2	=IF(AND(J$1>=FILTER($B$2:$B$3,($A$2:$A$3=$I$1)*($F$2:$F$3=$I2),""),K$1<=FILTER($D$2:$D$3,($A$2:$A$3=$I$1)*($F$2:$F$3=$I2),""),K$1<>""),"X","")
J6:P7	J6	=IF(AND(J$1>=FILTER($B$2:$B$3,($A$2:$A$3=$I$5)*($F$2:$F$3=$I6),""),K$1<=FILTER($D$2:$D$3,($A$2:$A$3=$I$5)*($F$2:$F$3=$I6),""),K$1<>""),"X","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J6:P7	Expression	=IF(AND(J$1>=FILTER($B$2:$B$3,($A$2:$A$3=$I$5)*($F$2:$F$3=$I6),""),K$1<=FILTER($D$2:$D$3,($A$2:$A$3=$I$5)*($F$2:$F$3=$I6),""),K$1<>""),"X","")="X"	text	NO
J2:P3	Expression	=IF(AND(J$1>=FILTER($B$2:$B$3,($A$2:$A$3=$I$1)*($F$2:$F$3=$I2),""),K$1<=FILTER($D$2:$D$3,($A$2:$A$3=$I$1)*($F$2:$F$3=$I2),""),K$1<>""),"X","")="X"	text	NO

Hi Dreid1011,

This is fantastic, when i add a second entry for worker 1 (different time) - it turns all the entries for worker 1 blank rather than filling in the X at both time slots.

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

Do you know why it would do that?

Thanks

 

[dreid1011]

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.