# Calculating how many hours taken between shifts

#### skoorBmaS

##### New Member
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

 Date Name Time In Time Out 23/05/2018 Phil 09:00 17:00 23/05/2018 Bill 09:00 17:00 23/05/2018 Gill 12:00 22:00 23/05/2018 Steve 10:00 17:00 23/05/2018 Dan 09:00 17:00 24/05/2018 Steve 09:00 17:00 24/05/2018 Phil 09:00 17:00 24/05/2018 Gill 07:00 15:00 24/05/2018 Dan 09:00 17: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

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### steve the fish

##### Well-known Member
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:

#### skoorBmaS

##### New Member
Works perfectly, thank you so much

1,102,690
Messages
5,488,280
Members
407,633
Latest member
varunwalla

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...