Conditional Format based on time difference

Diesel9a1

New Member
Joined
Feb 12, 2015
Messages
37
Employee IDNameStartFinish
161Paul10/08/2019 23:00:0011/08/2019 06:00:00
100Fred10/08/2019 23:00:0011/08/2019 06:00:00
224Jane11/08/2019 10:00:0011/08/2019 18:00:00
100John11/08/2019 15:00:0012/08/2019 03:00:00
161Paul11/08/2019 16:00:0012/08/2019 02:00:00

<tbody>
</tbody>


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?
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Hi, Below should work:

For Column C:

Code:
=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:
Code:
=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)

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Employee ID</td><td style=";">Name</td><td style=";">Start</td><td style=";">Finish</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">161</td><td style=";">Paul</td><td style="text-align: right;;">10/8/19 11:00 PM</td><td style="text-align: right;;">11/8/19 6:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">100</td><td style=";">Fred</td><td style="text-align: right;;">10/8/19 11:00 PM</td><td style="text-align: right;;">11/8/19 6:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">224</td><td style=";">Jane</td><td style="text-align: right;;">11/8/19 10:00 AM</td><td style="text-align: right;;">11/8/19 6:00 PM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">100</td><td style=";">John</td><td style="text-align: right;;">11/8/19 3:00 PM</td><td style="text-align: right;;">12/8/19 3:00 AM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">161</td><td style=";">Paul</td><td style="text-align: right;;">11/8/19 4:00 PM</td><td style="text-align: right;;">12/8/19 2:00 AM</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br />

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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,270
Messages
5,641,224
Members
417,202
Latest member
AndyVBA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top