Identifying consecutive bookings based three other criteria.

[ashbee]

Hello

I'm hoping someone can point me in the right direction, I'm trying to highlight rows where the same person has a booking at consecutive times at different sites. So in the example below, Person A has a 9:00am booking at Site 1 (first row) and then a 10am booking in site 2 (4th row). I'm trying to work out how to highlight this as the person won't have enough travel time to make both appointments.

Unfortunately the database used to set up the bookings wasn't set up very well and allowed different sites to be allocated without travel time in between, could anyone suggest a function I could use to highlight all the staff who have consecutive bookings on the same day in different locations? Many Thanks

Staff Location Date Time Duration
Person A Site1 21/02/2020 09:00 01:00
Person B Site2 21/02/2020 09:00 01:00
Person A Site2 21/02/2020 10:00 01:00
Person A Site2 22/02/2020 10:00 01:00
Person A Site1 21/02/2020 13:00 02:00

 

[Toadstool]

Hi Ashbee,

Does this Conditional Format do what you want?

Ashbee.xlsx
	A	B	C	D	E
1	Staff	Location	Date	Time	Duration
2	Person A	Site1	21/02/2020	9:00	1:00
3	Person B	Site2	21/02/2020	9:00	1:00
4	Person A	Site2	21/02/2020	10:00	1:00
5	Person A	Site2	22/02/2020	10:00	1:00
6	Person A	Site1	21/02/2020	13:00	2:00
7
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A16	Expression	=COUNTIFS($A$2:$A$999,A2,$C$2:$C$999,C2,$B$2:$B$999,"<>"&B2,$D$2:$D$999,D2+E2)	text	NO

 

[ashbee]

Hello Toadstool, just tried this and I'm obviously doing something wrong as it's highlighting the 2:00 duration cell. I'll play around and report back. Thank you for your suggestion.

 

[Toadstool]

Hello Toadstool, just tried this and I'm obviously doing something wrong as it's highlighting the 2:00 duration cell. I'll play around and report back. Thank you for your suggestion.

Is your data in the same columns as my example?
Did you apply the Conditional Format to column A and copy&paste in the formula?

 

[ashbee]

Hi again, yes as shown in the image but when I click OK the cell containing the word Staff is the only one that gets highlighted..

 

[Toadstool]

The formula should be applied to A2 and down as far as your data goes. By starting at A1 it offsets the highlight by one row.

 

[ashbee]

Ah, Toadstool, yes that worked, thank you so much.

Is there a way to edit the formula so that it highlights the offending rows rather than just the staff, so in the example above, I know that Person A has consecutive jobs across sites but I still need to look through all Person A's rows to see which two I need to modify... Might be pushing it and can do this manually if needed but would be great if I could.

You've helped me realise that conditional formatting is capable of much more than what I've been using it for so thank you again.

 

[Toadstool]

Ah, Toadstool, yes that worked, thank you so much.

Is there a way to edit the formula so that it highlights the offending rows rather than just the staff...

You're welcome!
Highlighting the second one impacted would be trickier as you'd need to check against every row with it's ending time.
Is it OK if I add a work column for that calculation?
NOTES:
I changed row 2 to start at 8am with 2 hours duration to show the challenge.
Instead of OR I could use two Conditional Format statements to highlight in two colours so it differentiates first from second.

Ashbee.xlsx
	A	B	C	D	E	F
1	Staff	Location	Date	Time	Duration	End Time
2	Person A	Site1	21/02/2020	8:00	2:00	10:00
3	Person B	Site2	21/02/2020	9:00	1:00	10:00
4	Person A	Site2	21/02/2020	10:00	1:00	11:00
5	Person A	Site2	22/02/2020	10:00	1:00	11:00
6	Person A	Site1	21/02/2020	13:00	2:00	15:00
7
Sheet3

Cell Formulas
Range	Formula
F2:F6	F2	=D2+E2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A999	Expression	=OR(COUNTIFS($A$2:$A$999,A2,$C$2:$C$999,C2,$B$2:$B$999,"<>"&B3,$D$2:$D$999,D3-E3),COUNTIFS($A$2:$A$999,A3,$C$2:$C$999,C2,$B$2:$B$999,"<>"&B2,$F$2:$F$999,D2))	text	NO

 

[Toadstool]

Actually I can do it without the work column but the formula becomes a little trickier:

Ashbee.xlsx
	A	B	C	D	E
1	Staff	Location	Date	Time	Duration
2	Person A	Site1	21/02/2020	8:00	2:00
3	Person B	Site2	21/02/2020	9:00	1:00
4	Person A	Site2	21/02/2020	10:00	1:00
5	Person A	Site2	22/02/2020	10:00	1:00
6	Person A	Site1	21/02/2020	13:00	2:00
7
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A999	Expression	=OR(COUNTIFS($A$2:$A$999,A2,$C$2:$C$999,C2,$B$2:$B$999,"<>"&B2,$D$2:$D$999,D2+E2),IFERROR(AGGREGATE(15,6,ROW($A$2:$A$999)-ROW($A$1)/(($A$2:$A$999=A2)*($C$2:$C$999=C2)*($B$2:$B$999<>B2)*($D$2:$D$999+$E$2:$E$999=D2)),1),FALSE))	text	NO

 

[ashbee]

Wow, that's fantastic, thank you. And by showing me it with and without a worker column I can try to understand and build on the formula. Thank you again, this is great.