Splitting hours for payroll purposes

[Neil Gerace]

Hi,

Problem: employees get paid different hourly rates at different times
of the day. From 00:00 to 08:00 is $NIGHTRATE, from 08:00 to 17:00 is
$DAYRATE, from 17:00 to 21:00 is $EVENINGRATE and from 21:00 to 24:00
is $NIGHTRATE again. As well as that there is an hourly surcharge of
$LOADING for working on Saturday or Sunday.

So someone working from 7:30 to 9:00 (1.5 hours) on a Sunday would get
paid 0.5 * $NIGHTRATE + 1 x $DAYRATE + 1.5 * $LOADING.

I want to do a spreadsheet that will calculate how many hours any
employee works during each of the four different periods of the day,
in order to work out their pay. The only input from the user should be
the date (to determine whether $LOADING applies) and the hours worked.
There is only 1 shift per worker per day, and shifts never span across
midnight.

Is there anything in Excel short of a mass of logical functions that
will split these timespans out easily and work this out for me?

 

[Pete_UK]

I think you will need to enter the start and end times, rather than
the hours worked.

You could have a user-defined function to do most of the calculations
and then just return a result to one column, but you might prefer to
see how the worked hours get split into the 4 different components by
having a column for each.

Hope this helps.

Pete

 

[Neil Gerace]

I think you will need to enter the start and end times, rather than
the hours worked.

Yes, you're right.

You could have a user-defined function to do most of the calculations
and then just return a result to one column, but you might prefer to
see how the worked hours get split into the 4 different components by
having a column for each.

I want to do it that way, but I get bogged down in a mass of logical
statements and layers of brackets. I know nothing about VB, perhaps it
is time I learned something?

 

[T. Valko]

shifts never span across midnight.

I'm assuming that means a person will *never* work from 7:00 PM to 3:00 AM
(as one example). This works based on that condition.

List the shifts and their hours:

...........G..............H.................I.......
1....................................................
2...Night......12:00 AM.....8:00 AM
3...Day.........8:00 AM......5:00 PM
4...Evening...5:00 PM.......9:00 PM
5...Night......9:00 PM........Note**

Note** - Enter this time as 24:00 and format the cell as TIME 1:30 PM. It
will *display in the cell* as 12:00 AM but if you look at its value in the
formula bar it will appear as 1/1/1900 12:00 AM.

A2 = start time
B2 = end time
C2 = total hours worked (this is needed for the shift split-out formula)

C2 formula:

=IF(COUNT(A2:B2)<2,0,(B2-A2+(B2<A2))*24)

List the shifts:

A10:A13 = Night, Day, Evening, Night

Enter this formula in B10 and copy down to B13:

=IF(C$2,IF(B$2+(B$2=0)<H2,0,IF(A$2>I2,0,IF(B$2+(B$2=0)>I2,I2,B$2+(B$2=0))-IF(A$2<H2,H2,A$2)))/(B$2+(B$2=0)-A$2)*C$2,0)

Format as GENERAL

The Total hours worked and the shift split-outs will return *decimal*
values:

A2 = 10:00 AM
B2 = 10:10 AM
C2 = 0.1666667

If you want TIME formats, C2 = 0:10...

Change the formula in C2 and remove the "*24" then format C2, B10:B13 as
h:mm

 

[Neil Gerace]

<snip>

Thanks Biff, I'll try that

I'm assuming that means a person will *never* work from 7:00 PM to 3:00 AM
(as one example).

That's right.