Calculating Total Time across Multiple Periods

[Shines1984]

Hi All,

We have introduced a new pay scale at work and I am really struggling to calculate hours correctly. Would really appreciate some help please.

I have the following, which allows for a shift start and end, as well as 3 separate break periods. The pay is determined by the time of day the staff member is working. I need to calculate the total number of hours worked in Rate A, Rate B, Rate C, and Rate D. Breaks need to be deducted from the rate of pay that the break was taken.

[TABLE="width: 630"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Break 1 Start[/TD]
[TD]Break 1 End[/TD]
[TD]Break 2 Start[/TD]
[TD]Break 2 End[/TD]
[TD]Break 3 Start[/TD]
[TD]Break 3 end[/TD]
[/TR]
[TR]
[TD="align: right"]22:00[/TD]
[TD="align: right"]08:00[/TD]
[TD="align: right"]01:00[/TD]
[TD="align: right"]01:15[/TD]
[TD="align: right"]03:15[/TD]
[TD="align: right"]03:30[/TD]
[TD="align: right"]06:45[/TD]
[TD="align: right"]06:30[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Rate A[/TD]
[TD]Rate B[/TD]
[TD]Rate C[/TD]
[TD]Rate D[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Start[/TD]
[TD="align: right"]06:30[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]23:00[/TD]
[TD="align: right"]03:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]23:00[/TD]
[TD="align: right"]03:00[/TD]
[TD="align: right"]06:30[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Hourly Rate[/TD]
[TD="align: right"]£8.50[/TD]
[TD="align: right"]£9.50[/TD]
[TD="align: right"]£10.75[/TD]
[TD="align: right"]£9.50[/TD]
[TD]
[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 630"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]


Is anyone able to help please?

 

[lennywatson]

I don't know if I can solve this or not, but I have a couple questions:
Are the times being added manually, or are they coming in from a punch clock, or what? Mostly wondering what format the numbers will be in (i.e. actual full excel date-and-time stamps, or just numbers typed in?
Will the time stamps be varying times, or are the shifts fixed?
Are the breaks fixed, or are they being added individually? (i.e. does everyone get 15 minutes break and have 15 minutes deducted, or do they punch out and in for break time?)

 

[lennywatson]

One more/rephrase: Are the break times set --i.e. can we assume that each break will always fall into its designated rate window? Or do you need to calculate any random break time?

 

[Shines1984]

One more/rephrase: Are the break times set --i.e. can we assume that each break will always fall into its designated rate window? Or do you need to calculate any random break time?

Hi Lenny,

Thanks for replying. I was able to solve this working in google sheets with some help from the google docs forum. We have the following formula:

=ARRAYFORMULA(IF(A8="",,(1/(24*60))*MMULT(TRANSPOSE(ROW(INDIRECT("A1:A"&FLOOR(60*24*((B8-A8)+N(B8<A8)))))^0),N(({TIMEVALUE((ROW(INDIRECT("A1:A"&FLOOR(60*24*((B8-A8)+N(B8<A8)))))-1)/(24*60)+A8)>=I$5:M$5}*{TIMEVALUE((ROW(INDIRECT("A1:A"&FLOOR(60*24*((B8-A8)+N(B8<A8)))))-1)/(24*60)+A8)<I$6:M$6}-{TIMEVALUE((ROW(INDIRECT("A1:A"&FLOOR(60*24*((B8-A8)+N(B8<A8)))))-1)/(24*60)+A8)>=C8}*{TIMEVALUE((ROW(INDIRECT("A1:A"&FLOOR(60*24*((B8-A8)+N(B8<A8)))))-1)/(24*60)+A8)<D8}-{TIMEVALUE((ROW(INDIRECT("A1:A"&FLOOR(60*24*((B8-A8)+N(B8<A8)))))-1)/(24*60)+A8)>=E8}*{TIMEVALUE((ROW(INDIRECT("A1:A"&FLOOR(60*24*((B8-A8)+N(B8<A8)))))-1)/(24*60)+A8)<F8}-{TIMEVALUE((ROW(INDIRECT("A1:A"&FLOOR(60*24*((B8-A8)+N(B8<A8)))))-1)/(24*60)+A8)>=G8}*{TIMEVALUE((ROW(INDIRECT("A1:A"&FLOOR(60*24*((B8-A8)+N(B8<A8)))))-1)/(24*60)+A8)<H8})>0))))