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
 

Some videos you may like

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
Joined
Oct 20, 2009
Messages
8,080
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:

Watch MrExcel Video

Forum statistics

Threads
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...
Top