Calculating working hours in a shift from first and last transactions

[jevans99]

Hi Guys,

I am trying to calculate the approximate hours worked by staff in a restaurant. The business operates in two shifts which ran from 9:30am to 4pm and then 6pm to close on Wednesday to Saturday and 9:30am to close on Sundays. Note that the start and finish times could have varies by circa one hour either side of the hours mentioned and that the close time could have been on the following day

I have all of the transactions from the tills and my plan is to simply take the time of the first and the last transactions to determine the hours. I have used a formula which calculates the hours on the early shift but because the late shift goes over to the next day I am unable to pick up the last working hour of the day and so unfortunately a min max function in isolation won't work.

I'd be super grateful for any help that you can give me.

Thanks in advance

 

[mrshl9898]

It's hard to help without specifics, something like

=IF(B2>(--"12:00 AM"),B2+1-A2,B2-A2)

Where A2 is the start time and B2 is the finish

 

[jevans99]

Th

It's hard to help without specifics, something like

=IF(B2>(--"12:00 AM"),B2+1-A2,B2-A2)

Where A2 is the start time and B2 is the finish

Thanks for your suggestion but i'm not entirely sure how that fits into working out the first and last transaction from a range of transactions? Does your formula need to be combined with some sort of Min / Max formula?

Is there a way that I can upload some sample data for you to review?

 

[mrshl9898]

You can either paste a screenshot, paste in a table or upload a workbook to a file sharing site and post the link.

The above works off a start and end time, so combining with MIN and MAX is likely, though hard to say without the data.

 

[jevans99]

Thanks.

I have taken a very small sample of the data which will hopefully help me better explain what I am looking to do.

31/12/2016	14:31
31/12/2016	15:58
31/12/2016	16:01
31/12/2016	19:57
31/12/2016	23:33
01/01/2017	00:18
01/01/2017	00:31
01/01/2017	10:00
01/01/2017	10:07

Essentially I'm trying to work out the total hours worked for each of the three shifts by taking the time of the first and last transaction in each shift period from each other. The shift patterns are 09:30am to 16:30pm and 18:00 to 03:00am Wednesday to Saturday and then 09:30am to 02:00am on Sundays.

I hope this helps and look forward to hearing your ideas.

 

[mrshl9898]

Will there only be a maximum one of each of the three shifts in each set?

If yes, assuming your data above is in A2:B10, perhaps.

C1 = DateTime
D1 = WeekDay
E1 = Shift
F1 = HoursPerShift

C2 = =A2+B2
D2 = =TEXT(WEEKDAY(A2),"Ddd")
E2 = =IF(AND(B2>=(--"09:30 AM"),B2<=(--"04:30 PM"),D2<>"Sun"),1,IF(OR(AND(B2<=(--"03:00 AM"),D2<>"Mon"),AND(B2>=(--"06:00 PM"),B2<=(--"11:59 PM"),D2<>"Sun")),2,IF(OR(AND(B2>=(--"09:30 AM"),D2="Sun"),AND(B2<=(--"03:00 AM"),D2="Mon")),3,"")))
F2 = =MAXIFS(C:C,E:E,E2)-MINIFS(C:C,E:E,E2)

 

[jevans99]

Will there only be a maximum one of each of the three shifts in each set?

If yes, assuming your data above is in A2:B10, perhaps.

C1 = DateTime
D1 = WeekDay
E1 = Shift
F1 = HoursPerShift

C2 = =A2+B2
D2 = =TEXT(WEEKDAY(A2),"Ddd")
E2 = =IF(AND(B2>=(--"09:30 AM"),B2<=(--"04:30 PM"),D2<>"Sun"),1,IF(OR(AND(B2<=(--"03:00 AM"),D2<>"Mon"),AND(B2>=(--"06:00 PM"),B2<=(--"11:59 PM"),D2<>"Sun")),2,IF(OR(AND(B2>=(--"09:30 AM"),D2="Sun"),AND(B2<=(--"03:00 AM"),D2="Mon")),3,"")))
F2 = =MAXIFS(C:C,E:E,E2)-MINIFS(C:C,E:E,E2)

This works beautifully but however there is two years worth of data and not just one weeks. Currently it is simply giving me three outputs for all of the data as I imagine it is simply giving me the longest early shift, the longest late shift and the longest Sunday shift for the last three years.

Any idea how I can get it to give me each of these per week?

 

[jevans99]

I think I need to insert a vlookup into the formulae so it looks at the date but i'm not sure how to do it, especially when the date could be the next day for late shifts.

 

[mrshl9898]

Got a bit stuck on longer time periods myself sorry. You are correct in your assumption of how it works.

Hopefully someone else will jump in with a solution.