Calculating how many hours taken between shifts

skoorBmaS

New Member
Joined
Feb 5, 2016
Messages
34
I have been looking all over the web for something similar to what I am looking for but have not found anything so far which suits what I need.

I am creating a time sheet where colleagues would input data for each employee, where they worked, start time and end time. As part of the law, we have to ensure that an employee has at least a 12 hour break between each shift. Does anyone know any way of doing this?

My data is entered randomly, so unfortunately the employees names are not nessicarily in order. As an example, see below

DateNameTime InTime Out
23/05/2018Phil09:0017:00
23/05/2018Bill09:0017:00
23/05/2018Gill12:0022:00
23/05/2018Steve10:0017:00
23/05/2018Dan09:0017:00
24/05/2018Steve09:0017:00
24/05/2018Phil09:0017:00
24/05/2018Gill07:0015:00
24/05/2018Dan09:0017:00

<tbody>
</tbody>

So as per the data above, I would like the formula to show that Gill has not had the allocated amount of rest, however I am not sure how to do this with the data being inputted in this way
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,829
Office Version
365
Platform
Windows
Try putting this in E2:

=IF(ISERROR(LOOKUP(2,1/($B$1:B1=B2),$A$1:A1)),"OK",IF(A2+C2-LOOKUP(2,1/($B$1:B1=B2),$A$1:A1+$D$1:D1)>=0.5,"OK","NOT ENOUGH BREAK"))

Edit: It assumes that you input data so that the latest data is at the bottom as shown. This is necessary for it to produce correct results.
 
Last edited:

Forum statistics

Threads
1,085,588
Messages
5,384,615
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top