Need Formula to Calculate Time/Overtime (2)

[Don Lowe]

I have created a formula that has two problems:

1. It only works it all the cells have a numerical value in them.
2. The formula is very long and needs to be simplified.

Is there anyone that can help me?

Here is the 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

Here is an example of what it looks like (Col J has the formula):
c d e f g h i j
Mon Tue Wed Thu Fri Sat Sun Total
Emp 2 15 0 0 0 0 0 0 19 (works)
Emp 3 15 85
Emp 4 76

 

[Niek Otten]

Look here:

http://www.cpearson.com/excel/overtime.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have created a formula that has two problems:
|
| 1. It only works it all the cells have a numerical value in them.
| 2. The formula is very long and needs to be simplified.
|
| Is there anyone that can help me?
|
| Here is the 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
|
| Here is an example of what it looks like (Col J has the formula):
| c d e f g h i j
| Mon Tue Wed Thu Fri Sat Sun Total
| Emp 2 15 0 0 0 0 0 0 19 (works)
| Emp 3 15 85
| Emp 4 76

 

[Don Lowe]

I have checked out http://www.cpearson.com/excel/overtime.htm. This is where
I got the concept behind the formula I put together. The formula used in the
website only works if every cell has a numerical value. Is there another
sight or a formula I am missing?

Thank you,

Don