Calculating Time In & Time Out and Break Time

[towong]

I am struggling to find a good way to achieve the following. Hoping someone can provide some sugguestion on this. Thank you in advance:

Employee Name

Employee Name	Time In_New	Time Out_New	Duration (Hours)
A	4/4/2019 23:30	4/4/2019 6:30	7
A	4/5/2019 9:00	4/5/2019 12:00	3
A	4/5/2019 14:00	4/5/2019 18:00	4
A	4/5/2019 19:00	4/5/2019 20:30	1.5
A	4/8/2019 8:30	4/8/2019 12:30	4
A	4/8/2019 14:30	4/8/2019 19:00	4.5
A	4/9/2019 10:00	4/9/2019 13:00	3
A	4/9/2019 14:00	4/9/2019 20:00	6
A	4/10/2019 12:00	4/10/2019 15:00	3
A	4/10/2019 16:00	4/10/2019 19:30	3.5
A	4/11/2019 12:30	4/11/2019 15:00	2.5

 In table above for employee A, on 4th-Apr, he worked from 4th Apr 23:30pm to 5th Apr 6:30am (i.e. he worked over from 1 day to the next. Then he started work again on 5th Apr from 9am to 12pm follow by a break of 2 hours and started working from 2pm to 6pm...so on and so forth. I need to have a report that is able to calculate the total hours of no break, anything less than 5 hours between out time and in time is considered as no break. So in the above scenario he worked from 4th Apr 23:30pm to 5th Apr 6:30am (worked across midnight from one day to the next), once this occur, I will need to look at whether he has any breaks of >5 hours the next day (i.e. 5th Apr). So as you can see employee A worked from 4th Apr till 5th Apr with no breaks in between of >= 5 hours till the end of day on 5th Apr.

So in this case I will need to flag this out like:

Employee Name	Day	Total Hours with no breaks
A	4/4/2019	15

[LukeM]

Hi @towong,

Please see attached for the solution to your problem. This uses multi-row formula tools to first calculate the break between the finish time and the next start time and then to calculate the cumulative working time as long as the break is less than 5 hours.

If you need you can then filter for where the Break flag is true to find the longest time employee A works before a break.

Let us know how you get on with this.

Luke

[afv2688 · Accepted solution]

Hello @towong ,

Hope this helps:

Cheers