Re: Splitting hours for payroll purposes

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


--
Biff
Microsoft Excel MVP


"Neil Gerace" <geracen@xxxxxxxxxxxxx> wrote in message
news:8e7092b3-0a55-4ceb-a6e2-334f93b7297c@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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?


.

Relevant Pages

  • Splitting hours for payroll purposes
    ... employees get paid different hourly rates at different times ... the date (to determine whether $LOADING applies) and the hours worked. ... There is only 1 shift per worker per day, ...
    (microsoft.public.excel.misc)
  • Crew scheduling/rostering problem, Some questions
    ... location and qualifications needed for executing this activity. ... qualifications and availability of the employees. ... Creating a number of anonymous shifts that covers all activities ...
    (comp.theory)
  • Crew scheduling/rostering problem, Some questions
    ... location and qualifications needed for executing this activity. ... qualifications and availability of the employees. ... Creating a number of anonymous shifts that covers all activities ...
    (comp.constraints)
  • Re: OT:Woops Wal-mart screwed up again!
    ... > punitive damages. ... > split shifts or short shifts. ... > apply to "full time" employees. ... To get the other side of the stories about Walmart. ...
    (rec.woodworking)
  • Re: OT:Woops Wal-mart screwed up again!
    ... punitive damages. ... As I understand it, the law is pretty simple, if you work for 6 hours, you ... split shifts or short shifts. ... apply to "full time" employees. ...
    (rec.woodworking)