Need Formula to Calculate Time/Overtime

[Don Lowe]

I need a formula to calculate time with overtime variances automaticly. The
following is how the time structure is set up:

Mon. - Thur. Fri. & Sat.
Sun.
1st (10) hours 1 1.5
2
..1 - 2 hrs. OT 1.5 1.5
2
over 2 hrs. OT 2 2
2

Each day the supervisor inputs the hours in perspective cells:
Mon. (J9), Tues. (M9), Wed. (P9), Thur. (S9), Fri. (V9), Sat. (Y9), Sun. (AB9)

In column AD (AD9) is where I need the calculation. Currently, I am
calculating time without overtime. I am using the following formula to
accomplish that:
=J9+M9+P9+S9+(V9*1.5)+(Y9*1.5)+(AB9*2)

This formula correlates with the 1st (10) hours only.

I need to some how integrate the overtime. Please help!!!

 

[FSt1]

hi
check this site out....
http://www.cpearson.com/excel/overtime.htm

regards
FSt1

 

[Don Lowe]

FSt1,

I checked out the website you mentioned.

Using the information given at that website I came up with the following
formula:

=(MIN(10,C21)+(IF(10<C21,C21-10,0)-IF(C21>12,C21-12,0))*1.5+(MAX(0,C21-12)*2))+(MIN(10,D21)+(IF(10<D21,D21-10,0)-IF(D21>12,D21-12,0))*1.5+(MAX(0,D21-12)*2))+(MIN(10,E21)+(IF(10<E21,E21-10,0)-IF(E21>12,E21-12,0))*1.5+(MAX(0,E21-12)*2))+(MIN(10,F21)+(IF(10<F21,F21-10,0)-IF(F21>12,F21-12,0))*1.5+(MAX(0,F21-12)*2))+((MIN(12,G21)*1.5)+(MAX(0,G21-12)*2))+((MIN(12,H21)*1.5)+(MAX(0,H21-12)*2))+(I21*2)

Given that:
Mon. (C21), Tues. (D21), Wed. (E21), Thur. (F21), Fri. (G21), Sat. (H21),
Sun. (I21)

Mon. - Thur. Fri. & Sat.
Sun.
1st (10) hours 1 1.5
2
..1 - 2 hrs. OT 1.5 1.5
2
over 2 hrs. OT 2 2
2

The formula works fine if all values are already put in. If anything is left
blank the numbers get all messed up. What am I messing? It looks like this:

c d e f g h i
Mon Tue Wed Thu Fri Sat Sun Total
Emp 2 15 0 0 0 0 0 0 19 (This one
is correct)
Emp 3 15 85
Emp 4 76

Additionally, the formula is really long. Is there a way to simplify the
formula?

Thank you for your help!

Don Lowe