Trying to calculate pay with variable Penalty Rates

[GoodShekky]

Hi,
Was working on a spreadsheet that calculates Pay at varying rates at different times of day

Ordinary Rate - Mon to Fri 7am-7pm
Mon to fri - 7pm to midnight
mon to fri - Midnight to 7am
sat -
sun -
Public Holiday

S0 basically the thing i'm having a problem with is Getting the hours worked at certain times..

for example.. Shift starts monday 6pm and finished 2am tuesday morning (3 different rates)

What would be the formula to work out the hours worked in each pay rate..?

also is there a way i can put a tickbox for public holiday rate .. (if clicked then pay x)

Cheers for your help..

[tim201110]

please fil in yellow cells

[GoodShekky]

oh one sec... read your reply wrong

[GoodShekky]

is this what u mean? sorry im only semi competent at using spreadsheets..

appreciate you helping

[tim201110]

morning =SUMPRODUCT(H3:H16*24,IFERROR(LOOKUP(F3:F16,$M$3:$M$14/($N$3:$N$14=H2),$Q$3:$Q$14),))
afternoon =SUMPRODUCT(H3:H16*24,IFERROR(LOOKUP(IF(B3:B16<C3:C16,F3:F16,G3:G16),$M$3:$M$14/($N$3:$N$14=I2),$Q$3:$Q$14),))
night =SUMPRODUCT(H3:H16*24,IFERROR(LOOKUP(G3:G16,$M$3:$M$14/($N$3:$N$14=J2),$Q$3:$Q$14),))
as array formulas
with helper columns

[GoodShekky]

Thanks so much. its pretty much exactly what i needed...
only thing i cant work out to do... is incorporate when there is a public holiday.. into the additions I've made (to find out how much to be paid.. not really an issue though.. easy enough for me to add it on manually when i need to.. thanks again

[tim201110]

working days
=SUM(($F$3:$F$16<6)*($G$3:$G$16<6)*$H$3:$J$16*($H$2:$J$2=F20))*24 as arraay

[GoodShekky]

Hey, just came across a situation where a small error is made.... If u look at the 6/1/17, there is 10 hour 10 minutes worked...
The 1 hour and 10 minutes past 7am has not been added.. leaving the totals in 'C1' & 'K19' same value

Is there a way to add the extra to 'I9' so that it adds the appropriate accrual from 2 different shifts..?

cheers

[tim201110]

=sumproduct(j3:m3*24,lookup(f3:i3,$q$3:$q$14/($r$3:$r$14=$j$2:$m$2),$u$3:$u$14))

[symbolsoup]

Thank you tim201110 for this solution. It answers my question perfectly.

GoodShekky, we must be on the exact same award as your shift breakdowns are the same as mine. Thanks for asking the question!

[symbolsoup]

=sumproduct(j3:m3*24,lookup(f3:i3,$q$3:$q$14/($r$3:$r$14=$j$2:$m$2),$u$3:$u$14))

Your latest revision got me 95% of the way, but I need to factor in lunch breaks.

I have added a column (D) with an IF statement to apply a 30 minute lunch break when working 6 hours or more.

Please Login or Register  to view this content.

That works fine at this stage, but I cannot find a way to minus the lunch break in your formulas to correctly reduce the total hours worked.

What's the best way to attack this?