# Calculating working hours in a shift from first and last transactions

#### jevans99

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

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

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
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

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

Replies
3
Views
151
Replies
8
Views
115
Replies
6
Views
449
Replies
7
Views
143
Replies
2
Views
360

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.

### Which adblocker are you using?

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

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