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" wrote in message
...
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?