Calculating working hours in a shift from first and last transactions

jevans99

New Member
Joined
Feb 26, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,784
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

New Member
Joined
Feb 26, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 6, 2012
Messages
1,784
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

New Member
Joined
Feb 26, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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/201615:58
31/12/201616:01
31/12/201619:57
31/12/201623:33
01/01/201700:18
01/01/201700:31
01/01/201710:00
01/01/201710: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

Well-known Member
Joined
Feb 6, 2012
Messages
1,784
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

New Member
Joined
Feb 26, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 26, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 6, 2012
Messages
1,784
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,920
Messages
5,638,995
Members
417,061
Latest member
thematulaak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top