Conditional Format based on time difference

[Diesel9a1]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee ID[/TD]
[TD]Name[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[/TR]
[TR]
[TD]161[/TD]
[TD]Paul[/TD]
[TD]10/08/2019 23:00:00[/TD]
[TD]11/08/2019 06:00:00[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Fred[/TD]
[TD]10/08/2019 23:00:00[/TD]
[TD]11/08/2019 06:00:00[/TD]
[/TR]
[TR]
[TD]224[/TD]
[TD]Jane[/TD]
[TD]11/08/2019 10:00:00[/TD]
[TD]11/08/2019 18:00:00[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]John[/TD]
[TD]11/08/2019 15:00:00[/TD]
[TD]12/08/2019 03:00:00[/TD]
[/TR]
[TR]
[TD]161[/TD]
[TD]Paul[/TD]
[TD]11/08/2019 16:00:00[/TD]
[TD]12/08/2019 02:00:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Looking for excel to do an auto conditional format and highlight issues where the same employee starts their next shift within 12 hours of them finishing their previous shift. Easy if the table is sorted by name then start date, as it could simply minus the finish of previous shift from the start of current shift and if less than 12 hours, highlight in red. But the table is as is with regards the entry. There could be many employees in between the two entries of the same user (in our case Paul). Also need to cater for shifts going over midnight etc.

Any thoughts please?

 

[Aryatect]

Hi, Below should work:

For Column C:

=IFERROR(AGGREGATE(14,6,--((($D$2:$D$6-C2)/((B2=$B$2:$B$6)*(ROW(B2)<>ROW($B$2:$B$6))))<0.5),1)>0,FALSE)

For Column D:

=IFERROR(AGGREGATE(14,6,--(((D2-$C$2:$C$6)/((B2=$B$2:$B$6)*(ROW(B2)<>ROW($B$2:$B$6))))<0.5),1)>0,FALSE)

Book1
	A	B	C	D
1	Employee ID	Name	Start	Finish
2	161	Paul	10/8/19 11:00 PM	11/8/19 6:00 AM
3	100	Fred	10/8/19 11:00 PM	11/8/19 6:00 AM
4	224	Jane	11/8/19 10:00 AM	11/8/19 6:00 PM
5	100	John	11/8/19 3:00 PM	12/8/19 3:00 AM
6	161	Paul	11/8/19 4:00 PM	12/8/19 2:00 AM
Sheet3

Screenshots:

https://drive.google.com/open?id=1QvtnigmDgZYGyS8QUQtNHirxAMSY3YBY

https://drive.google.com/open?id=1aAKTJm7d-ptmM5O-BEZF8g23vYexGdRN

https://drive.google.com/open?id=10Nb8tw1p3VvRvhqwm4eEiTtFyZPN_QnJ

https://drive.google.com/open?id=1nEtPXMqT1GqTVu8SkiW9v8dBuw8MjTWX

https://drive.google.com/open?id=1CehJptuwHjLlHO075F_p_l20vGkNuXuN

 

[Diesel9a1]

Thanks for looking at this one Aryatect. I've tried to implement it, but the majority of the cells in the columns end up being coloured.
https://drive.google.com/open?id=1MATir2XC013XoEij9JkY81DdnDUUxKsm

https://drive.google.com/open?id=1NUn9iUfeZkOeUysLgMqFeyAjggQeGtJX

https://drive.google.com/open?id=1yOTLZY8-OXHtp_PpDo25ynN-V4gdAvTY