Need help with conditional rates and roster times for payroll

[Ricky]

Hi everyone,
I'm trying to to create a payroll using rosters but I am having trouble
trying to define different rates with certain times
For example, if a person works from 19:00 - 0:00 they will get $1 extra and
if they work between 0:00 and 7:00 they will get $2 extra.
So if they're rostered for 18:00 - 8:00 they should effectively get $5 + $14
= $19 extra
The problem i am having is that i cant seem to calculate the hours properly.
It has been suggested i should if statements like =IF(A1 * 24 > 7, IF(A1 *
24 > 19, "1", "0"), "2") or using lookup tables but they only calculate the
rate you get for a certain hour and using only 1 cell reference.

I think the easiest way is to calculate the number of hours that will get
you extra pay from the start and finish time as long as it is between those
specified hours. because after you work that out all you have to do is
multiply that with the extra money and then sum it up. If anyone has any
ideas on how i could achieve this it would be greatly appreciated. Thanks!

 

[Guest]

This may not be the easiest way, but it seemed to work for me.

Let's say you put the start time in A2, end time in B2. In C2 type
"=IF(B2<A2,B2+24,B2)". This is for when a person starts before midnight
but ends the next day. In E1-L1 put 0-7 respectively, and M1-T1 put 24-31
(for when they start before midnight and end the next day). In E2 the
formula would be "=IF(E$1>$A2,IF(E1<$C2,2,0),0)". This puts a 2 in the cell
if they happened to work that specific hour. Copy this formula into F2-T2.
You can do something similar for the $1 hours, lets say you start in U1
putting 19, in U2 you would put "=IF(U$1>$A2,IF(U1<$C2,1,0),0)".