Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Formula to Calculate Time/Overtime (2)
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(C2112,C21-12,0))*1.5+(MAX(0,C21-12)*2))+(MIN(10,D21)+(IF(10<D21,D21-10,0)-IF(D2112,D21-12,0))*1.5+(MAX(0,D21-12)*2))+(MIN(10,E21)+(IF(10<E21,E21-10,0)-IF(E2112,E21-12,0))*1.5+(MAX(0,E21-12)*2))+(MIN(10,F21)+(IF(10<F21,F21-10,0)-IF(F2112,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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Formula to Calculate Time/Overtime (2)
Look he
http://www.cpearson.com/excel/overtime.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Don Lowe" wrote in message ... |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(C2112,C21-12,0))*1.5+(MAX(0,C21-12)*2))+(MIN(10,D21)+(IF(10<D21,D21-10,0)-IF(D2112,D21-12,0))*1.5+(MAX(0,D21-12)*2))+(MIN(10,E21)+(IF(10<E21,E21-10,0)-IF(E2112,E21-12,0))*1.5+(MAX(0,E21-12)*2))+(MIN(10,F21)+(IF(10<F21,F21-10,0)-IF(F2112,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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Formula to Calculate Time/Overtime (2)
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 "Niek Otten" wrote: Look he http://www.cpearson.com/excel/overtime.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Don Lowe" wrote in message ... |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(C2112,C21-12,0))*1.5+(MAX(0,C21-12)*2))+(MIN(10,D21)+(IF(10<D21,D21-10,0)-IF(D2112,D21-12,0))*1.5+(MAX(0,D21-12)*2))+(MIN(10,E21)+(IF(10<E21,E21-10,0)-IF(E2112,E21-12,0))*1.5+(MAX(0,E21-12)*2))+(MIN(10,F21)+(IF(10<F21,F21-10,0)-IF(F2112,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Overtime double time | New Users to Excel | |||
How can i calculate overtime give formula for 24 hrs clock | Excel Discussion (Misc queries) | |||
Need Formula to Calculate Time/Overtime | Excel Discussion (Misc queries) | |||
if then formula for total time ;overtime hours versus regular | Excel Worksheet Functions | |||
Could you help me to calculate overtime | Excel Worksheet Functions |