Timesheet with many conditions

[DawnTreader]

Hello all

i am working on a spreadsheet that i need to calculate all sorts of
conditions. i want to automate a the entry and ability to breakdown the
information. these timesheets are used by our technicians who travel and this
is part of the problem.

first my companies policy is 8 hours a day is regular time, then 3 hours a
day above that is overtime, everything worked above 11 hours is doubletime.
overtime doesnt happen until 40 hours a week are met. i need to have a
section of my spreadsheet shows this all broken down. so for example if i
have someone work 12 hours a day everyday of the week i need to show how many
hours are reg, overtime, doubletime each day. This is what it looks like when
i break it down by the policy:

Date Hours Reg OT DT
Sunday 12 8 3 1
Monday 12 8 3 1
tuesday 12 8 3 1
wednesday 12 8 3 1
Thursday 12 8 3 1
Friday 12 3 9
Saturday 12 3 9

Total 84 40 21 23

the example is broken down by hand mind you. i want excel to calculate this
itself. i am usually pretty good at excel, but this problem has me stumped.
below is another example that needs to work at the same time as the one above
does:

Date Hours Reg OT DT
Sunday 7 7
Monday 7 7
Tuesday 7 7
Wednesday 7 7
Thursday 7 7
Friday 7 5 2
Saturday 7 3 4

Total 49 40 5 4

can anyone help with this problem?

 

[Bob Phillips]

C2:
=IF(SUM($C$1:C1)+IF(SUM($B$2:$B$8)>40,8,B2)>40,40-SUM($C$1:C1),IF(SUM($B$2:$B$8)>40,MIN(B2,8),B2))
D2: =MIN(3,IF(SUM($B$2:$B$8)>40,B2-8,0))
E2: =B2-C2-D2

and copy down

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[DawnTreader]

Hello

so i got the first part doing what i want. it gives me a nice breakdown of
the hours of the employee into regular, overtime and double time and it
figures out when those start applying.

now i have an additional problem. i have to categorize hours because the
technicians get different rates depending on where they were when they worked
those hours.

the categories are Local, Away and Travel. so if a technician was out of
town it is categorized by the fact that he was Away. this category gets a
different pay rate then if they were working the same amount of hours in the
Local category and this effects the amount they get paid for overtime and
double time as well.

what that means is if someone could work 8 hours locally, travel for 8
hours, and then work for another 8 hours in the away category they would have
to be paid according to the rate for each according to the type of hours
those hours were.

here is 2 examples to show my conundrum:

local travel away
Mon 8
Mon 8
Mon 8
Tue 8
Tue 8
Tue 8
Wed 8
Wed 8
Wed 8

notice that monday local is first, tuesday travel is first and wednesday the
away time is first. each situation would cause different rates for the the OT.

so now i need a formula that puts the hours into category columns of Local
Reg, Local OT, Local DT, Travel, Away Reg, Away OT, and Away DT. basically
the reason for all this is to allow accounting the ability to look at the
time sheet and know exactly what to pay the technician.

basically i need excel to lay this out in a grid and figure out where to put
how many hours, without user intervention.

if there is anyone who can help, it would be much appreciated, and i can
email the spreadsheet i am working on if that would help.

i am so tempted to turn this into an access database, but that would take
longer than i have to fix this. thanks in advance.

 

[GSurg0000]

Hello

so i got thefirstpart doing what i want. it gives me a nice breakdown of
the hours of the employee into regular, overtime and double time and it
figures out when those start applying.

now i have an additional problem. i have to categorize hours because the
technicians get different rates depending on where they were when they worked
those hours.

the categories are Local, Away and Travel. so if a technician was out of
town it is categorized by the fact that he was Away. this category gets a
different pay rate then if they were working the same amount of hours in the
Local category and this effects the amount they get paid for overtime and
double time as well.

what that means is if someone could work 8 hours locally, travel for 8
hours, and then work for another 8 hours in the away category they would have
to be paid according to the rate for each according to the type of hours
those hours were.

here is 2 examples to show my conundrum:

        local   travel  away
Mon     8              
Mon             8      
Mon                     8
Tue             8      
Tue                     8
Tue     8              
Wed                     8
Wed             8      
Wed     8              

notice thatmondaylocal isfirst, tuesday travel isfirstand wednesday the
away time isfirst. each situation would cause different rates for the the OT.

so now i need a formula that puts the hours into category columns of Local
Reg, Local OT, Local DT, Travel, Away Reg, Away OT, and Away DT. basically
the reason for all this is to allow accounting the ability to look at the
time sheet and know exactly what to pay the technician.

basically i need excel to lay this out in a grid and figure out where to put
how many hours, without user intervention.

if there is anyone who can help, it would be much appreciated, and i can
email the spreadsheet i am working on if that would help.

i am so tempted to turn this into anaccessdatabase, but that would take
longer than i have to fix this. thanks in advance.

Dawn
For what it's worth, I'm currently working on an Access database to do
this so I've been checking employment laws. Not sure about your state
but in California, you must pay overtime on >8hrs a day regardless of
whether or not 40hrs/wk criterion is reached unless employee is in an
exempted category.

 

[DawnTreader]

Hello GSurg

i guess i should mention that in BC Canada according to our accountant they
must have a total of 40 hours before overtime starts. it could also be the
nature of the business and the way our workers are on shifts.

thanks but wrong country.