If cell matches another cell, apply conditional formatting to another cell

[SuperX7]

Hi everyone...

This one's stumped me. I'm trying to create a staff roster where employees are selected from a drop down list.

The issue is that there could be instances where the same employee (in this case Employee 1) is working in a different location so may be listed elsewhere.

Is it possible, to conditional format the start and finish times IF the employee is listed elsewhere AND if the times fall within the same time window to avoid "double booking" or incorrect scheduling?

For example below I've managed to conditional format

B58:
=AND(B58>B48,B58<C48) - 1200 is after 1000 and before 1800

C58:
=(C58<C48) - 1700 is before 1800

Which indicates the schedule isn't possible as the times fall within ROW 48 with the same employee

This may be a long shot but I have to ask the experts here as its driving me nuts!

 

[Eric W]

How about:

Book1
	A	B	C
1	Name	Start	End
2	Employee 1	10:00	18:00
3
4	Employee 2	8:00	10:00
5
6	Employee 3	8:00	10:00
7
8	Employee 3	12:00	14:00
9
10	Employee 3	9:30	11:00
11
12	Employee 1	12:00	17:00
13
14
15
16
17
18
19
20
Sheet10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C20	Expression	=SUM(($A$2:$A$20=$A2)*(ROW($A$2:$A$20)<>ROW(A2))*(IF($C$2:$C$20<$C2,$C$2:$C$20,$C2)-IF($B$2:$B$20>$B2,$B$2:$B$20,$B2)>0))	text	NO

 

[SuperX7]

How about:

Book1
	A	B	C
1	Name	Start	End
2	Employee 1	10:00	18:00
3
4	Employee 2	8:00	10:00
5
6	Employee 3	8:00	10:00
7
8	Employee 3	12:00	14:00
9
10	Employee 3	9:30	11:00
11
12	Employee 1	12:00	17:00
13
14
15
16
17
18
19
20
Sheet10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C20	Expression	=SUM(($A$2:$A$20=$A2)*(ROW($A$2:$A$20)<>ROW(A2))*(IF($C$2:$C$20<$C2,$C$2:$C$20,$C2)-IF($B$2:$B$20>$B2,$B$2:$B$20,$B2)>0))	text	NO

Hi Eric,

This is amazing, thank you... one thing I did notice is that if I put a time outside midnight it won't highlight?

eg.
EMP1
Start - 1000
End - 2100

EMP2
Start - 2000
End - 0200

They should in theory highlight?

 

[Eric W]

Well, not necessarily. First, those 2 won't highlight because they are different employees. But even if those were the same employee, they wouldn't highlight. The issue is that the formula does not know that the 0200 time is for the next day. One way to handle this is to enter the date as part of the time:

Book1
	A	B	C
1	Name	Start	End
2	Employee 1	11/19/24 10:00	11/19/24 21:00
3
4	Employee 2	11/19/24 8:00	11/19/24 10:00
5
6	Employee 3	11/19/24 8:00	11/19/24 10:00
7
8	Employee 3	11/19/24 12:00	11/19/24 14:00
9
10	Employee 3	11/19/24 9:30	11/19/24 11:00
11
12	Employee 1	11/19/24 20:00	11/20/24 2:00
Sheet2

I'm not sure what this does to your entry methodology, but it is clear. The other option is to change the formula so that if the time in the end column is less than the time in the start column, we assume that it's the next day:

Book1
	A	B	C
1	Name	Start	End
2	Employee 1	10:00	21:00
3
4	Employee 2	8:00	10:00
5
6	Employee 3	8:00	10:00
7
8	Employee 3	12:00	14:00
9
10	Employee 3	9:30	11:00
11
12	Employee 1	20:00	2:00
13
14
15
16
17
18
19
20
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:C20	Expression	=SUM(($A$2:$A$20=$A2)*(ROW($A$2:$A$20)<>ROW(A2))*(IF($C$2:$C$20+($C$2:$C$20<$B$2:$B$20)<$C2+($C2<$B2),$C$2:$C$20+($C$2:$C$20<$B$2:$B$20),$C2+($C2<$B2))-IF($B$2:$B$20>$B2,$B$2:$B$20,$B2)>0))	text	NO

A bit more complicated but viable. Also, please update your profile to show the version of Excel you're using. I could have made this a bit shorter if you're using a newer version, but I didn't want to assume.

 

[SuperX7]

Hi Eric!

Thanks - my apologies my response contained a typo regarding the EMP numbers (they were both supposed to be the same person). I'll give the 2nd solution you've provided a go and report back!