|
Help with calculating time
Hello,
I have an Excel to track performance. Sheet1 is performance table where user input their ID, date, part#, side, start sequence & stop sequence daily. Then on a reference sheet2 with field: part#, side, sequence#, time (calculated). What I would like to calculate is look at the start & stop sequence based on the part# & side then calculate how long (time) the operator spend on the job (part#). Thanks Example: Sheet1 - User input data ID date part# side start seq stop seq 001 1/8/08 1360-1 1st 100 500 002 1/10/08 1560-3 2nd 600 1000 Sheet2 - Reference part# side start seq time (min) 1360-1 1st 100 5 1360-1 1st 200 10 1360-1 1st 300 10 1360-1 1st 400 30 1360-1 1st 500 15 1360-1 1st 600 60 1560-3 2nd 100 10 ........... Result Sheet3 ID date part# side start seq stop seq Total time (calculated) 001 1/8/08 1360-1 1st 100 500 70 How should i go about linking the two sheets so it will calculate the time based on when he start the sequence to when he stop the sequence? Thanks |
Help with calculating time
Try this:
With this data on Sheet2 in the range A2:D8 1360-1...1st...100...5 1360-1...1st...200...10 1360-1...1st...300...10 1360-1...1st...400...30 1360-1...1st...500...15 1360-1...1st...600...60 1560-3...2nd...100...10 With this data on Sheet3 in the range A2:F2 001...1/8/08...1360-1...1st...100...500 Enter this formula on Sheet3 G2: =SUMPRODUCT(--(Sheet2!A$2:A$8=C2),--(Sheet2!B$2:B$8=D2),--(Sheet2!C$2:C$8=E2),--(Sheet2!C$2:C$8<=F2),Sheet2!D$2:D$8) -- Biff Microsoft Excel MVP "Cam" wrote in message ... Hello, I have an Excel to track performance. Sheet1 is performance table where user input their ID, date, part#, side, start sequence & stop sequence daily. Then on a reference sheet2 with field: part#, side, sequence#, time (calculated). What I would like to calculate is look at the start & stop sequence based on the part# & side then calculate how long (time) the operator spend on the job (part#). Thanks Example: Sheet1 - User input data ID date part# side start seq stop seq 001 1/8/08 1360-1 1st 100 500 002 1/10/08 1560-3 2nd 600 1000 Sheet2 - Reference part# side start seq time (min) 1360-1 1st 100 5 1360-1 1st 200 10 1360-1 1st 300 10 1360-1 1st 400 30 1360-1 1st 500 15 1360-1 1st 600 60 1560-3 2nd 100 10 .......... Result Sheet3 ID date part# side start seq stop seq Total time (calculated) 001 1/8/08 1360-1 1st 100 500 70 How should i go about linking the two sheets so it will calculate the time based on when he start the sequence to when he stop the sequence? Thanks |
Help with calculating time
Hi Valko,
I tried the formula you suggested, but it returns "0" where it shouldn't be. "T. Valko" wrote: Try this: With this data on Sheet2 in the range A2:D8 1360-1...1st...100...5 1360-1...1st...200...10 1360-1...1st...300...10 1360-1...1st...400...30 1360-1...1st...500...15 1360-1...1st...600...60 1560-3...2nd...100...10 With this data on Sheet3 in the range A2:F2 001...1/8/08...1360-1...1st...100...500 Enter this formula on Sheet3 G2: =SUMPRODUCT(--(Sheet2!A$2:A$8=C2),--(Sheet2!B$2:B$8=D2),--(Sheet2!C$2:C$8=E2),--(Sheet2!C$2:C$8<=F2),Sheet2!D$2:D$8) -- Biff Microsoft Excel MVP "Cam" wrote in message ... Hello, I have an Excel to track performance. Sheet1 is performance table where user input their ID, date, part#, side, start sequence & stop sequence daily. Then on a reference sheet2 with field: part#, side, sequence#, time (calculated). What I would like to calculate is look at the start & stop sequence based on the part# & side then calculate how long (time) the operator spend on the job (part#). Thanks Example: Sheet1 - User input data ID date part# side start seq stop seq 001 1/8/08 1360-1 1st 100 500 002 1/10/08 1560-3 2nd 600 1000 Sheet2 - Reference part# side start seq time (min) 1360-1 1st 100 5 1360-1 1st 200 10 1360-1 1st 300 10 1360-1 1st 400 30 1360-1 1st 500 15 1360-1 1st 600 60 1560-3 2nd 100 10 .......... Result Sheet3 ID date part# side start seq stop seq Total time (calculated) 001 1/8/08 1360-1 1st 100 500 70 How should i go about linking the two sheets so it will calculate the time based on when he start the sequence to when he stop the sequence? Thanks |
Help with calculating time
You'll have to post a sample of data and show where the formula is returning
a 0. -- Biff Microsoft Excel MVP "Cam" wrote in message ... Hi Valko, I tried the formula you suggested, but it returns "0" where it shouldn't be. "T. Valko" wrote: Try this: With this data on Sheet2 in the range A2:D8 1360-1...1st...100...5 1360-1...1st...200...10 1360-1...1st...300...10 1360-1...1st...400...30 1360-1...1st...500...15 1360-1...1st...600...60 1560-3...2nd...100...10 With this data on Sheet3 in the range A2:F2 001...1/8/08...1360-1...1st...100...500 Enter this formula on Sheet3 G2: =SUMPRODUCT(--(Sheet2!A$2:A$8=C2),--(Sheet2!B$2:B$8=D2),--(Sheet2!C$2:C$8=E2),--(Sheet2!C$2:C$8<=F2),Sheet2!D$2:D$8) -- Biff Microsoft Excel MVP "Cam" wrote in message ... Hello, I have an Excel to track performance. Sheet1 is performance table where user input their ID, date, part#, side, start sequence & stop sequence daily. Then on a reference sheet2 with field: part#, side, sequence#, time (calculated). What I would like to calculate is look at the start & stop sequence based on the part# & side then calculate how long (time) the operator spend on the job (part#). Thanks Example: Sheet1 - User input data ID date part# side start seq stop seq 001 1/8/08 1360-1 1st 100 500 002 1/10/08 1560-3 2nd 600 1000 Sheet2 - Reference part# side start seq time (min) 1360-1 1st 100 5 1360-1 1st 200 10 1360-1 1st 300 10 1360-1 1st 400 30 1360-1 1st 500 15 1360-1 1st 600 60 1560-3 2nd 100 10 .......... Result Sheet3 ID date part# side start seq stop seq Total time (calculated) 001 1/8/08 1360-1 1st 100 500 70 How should i go about linking the two sheets so it will calculate the time based on when he start the sequence to when he stop the sequence? Thanks |
Help with calculating time
Valko,
I got the results, but the calculation is off little. The calculated cell are in column G of sheet1. The last info is what it should be calculated values. For example, sheet1 row 2 should be equal 55 cause it see start (seq) to stop (seq) is 100 to 400 so it should sum all value from row 2 to 5 (5+10+10+30) in column D from Ref sheet. Sheet1 - Data sheet A B C D E F G ID date part# side start stop Cal Time Should be: 1 1/8/08 1360-1 1st 100 400 50 55 (5+10+10+30) 2 1/10/08 1360-1 1st 300 500 125 65 (10+30+25) 3 1/10/08 1360-1 1st 100 600 135 140 (5+10+10+30+25+60) Ref - Ref sheet A B C D part# side seq time 1360-1 1st 100 5 1360-1 1st 200 10 1360-1 1st 300 10 1360-1 1st 400 30 1360-1 1st 500 25 1360-1 1st 600 60 1560-3 2nd 100 10 "T. Valko" wrote: You'll have to post a sample of data and show where the formula is returning a 0. -- Biff Microsoft Excel MVP "Cam" wrote in message ... Hi Valko, I tried the formula you suggested, but it returns "0" where it shouldn't be. "T. Valko" wrote: Try this: With this data on Sheet2 in the range A2:D8 1360-1...1st...100...5 1360-1...1st...200...10 1360-1...1st...300...10 1360-1...1st...400...30 1360-1...1st...500...15 1360-1...1st...600...60 1560-3...2nd...100...10 With this data on Sheet3 in the range A2:F2 001...1/8/08...1360-1...1st...100...500 Enter this formula on Sheet3 G2: =SUMPRODUCT(--(Sheet2!A$2:A$8=C2),--(Sheet2!B$2:B$8=D2),--(Sheet2!C$2:C$8=E2),--(Sheet2!C$2:C$8<=F2),Sheet2!D$2:D$8) -- Biff Microsoft Excel MVP "Cam" wrote in message ... Hello, I have an Excel to track performance. Sheet1 is performance table where user input their ID, date, part#, side, start sequence & stop sequence daily. Then on a reference sheet2 with field: part#, side, sequence#, time (calculated). What I would like to calculate is look at the start & stop sequence based on the part# & side then calculate how long (time) the operator spend on the job (part#). Thanks Example: Sheet1 - User input data ID date part# side start seq stop seq 001 1/8/08 1360-1 1st 100 500 002 1/10/08 1560-3 2nd 600 1000 Sheet2 - Reference part# side start seq time (min) 1360-1 1st 100 5 1360-1 1st 200 10 1360-1 1st 300 10 1360-1 1st 400 30 1360-1 1st 500 15 1360-1 1st 600 60 1560-3 2nd 100 10 .......... Result Sheet3 ID date part# side start seq stop seq Total time (calculated) 001 1/8/08 1360-1 1st 100 500 70 How should i go about linking the two sheets so it will calculate the time based on when he start the sequence to when he stop the sequence? Thanks |
Help with calculating time
Valko,
Also forgot to mentioned that column G in sheet1 result is the calculated values based on your sugguested formula. For cell G2 in sheet1. =SUMPRODUCT(--(Ref!A$2:A$8=C2),--(Ref!B$2:B$8=D2),--(Ref!C$2:C$8=E2),--(Ref!C$2:C$8<=F2),Ref!D$2:D$8) "Cam" wrote: Valko, I got the results, but the calculation is off little. The calculated cell are in column G of sheet1. The last info is what it should be calculated values. For example, sheet1 row 2 should be equal 55 cause it see start (seq) to stop (seq) is 100 to 400 so it should sum all value from row 2 to 5 (5+10+10+30) in column D from Ref sheet. Sheet1 - Data sheet A B C D E F G ID date part# side start stop Cal Time Should be: 1 1/8/08 1360-1 1st 100 400 50 55 (5+10+10+30) 2 1/10/08 1360-1 1st 300 500 125 65 (10+30+25) 3 1/10/08 1360-1 1st 100 600 135 140 (5+10+10+30+25+60) Ref - Ref sheet A B C D part# side seq time 1360-1 1st 100 5 1360-1 1st 200 10 1360-1 1st 300 10 1360-1 1st 400 30 1360-1 1st 500 25 1360-1 1st 600 60 1560-3 2nd 100 10 "T. Valko" wrote: You'll have to post a sample of data and show where the formula is returning a 0. -- Biff Microsoft Excel MVP "Cam" wrote in message ... Hi Valko, I tried the formula you suggested, but it returns "0" where it shouldn't be. "T. Valko" wrote: Try this: With this data on Sheet2 in the range A2:D8 1360-1...1st...100...5 1360-1...1st...200...10 1360-1...1st...300...10 1360-1...1st...400...30 1360-1...1st...500...15 1360-1...1st...600...60 1560-3...2nd...100...10 With this data on Sheet3 in the range A2:F2 001...1/8/08...1360-1...1st...100...500 Enter this formula on Sheet3 G2: =SUMPRODUCT(--(Sheet2!A$2:A$8=C2),--(Sheet2!B$2:B$8=D2),--(Sheet2!C$2:C$8=E2),--(Sheet2!C$2:C$8<=F2),Sheet2!D$2:D$8) -- Biff Microsoft Excel MVP "Cam" wrote in message ... Hello, I have an Excel to track performance. Sheet1 is performance table where user input their ID, date, part#, side, start sequence & stop sequence daily. Then on a reference sheet2 with field: part#, side, sequence#, time (calculated). What I would like to calculate is look at the start & stop sequence based on the part# & side then calculate how long (time) the operator spend on the job (part#). Thanks Example: Sheet1 - User input data ID date part# side start seq stop seq 001 1/8/08 1360-1 1st 100 500 002 1/10/08 1560-3 2nd 600 1000 Sheet2 - Reference part# side start seq time (min) 1360-1 1st 100 5 1360-1 1st 200 10 1360-1 1st 300 10 1360-1 1st 400 30 1360-1 1st 500 15 1360-1 1st 600 60 1560-3 2nd 100 10 .......... Result Sheet3 ID date part# side start seq stop seq Total time (calculated) 001 1/8/08 1360-1 1st 100 500 70 How should i go about linking the two sheets so it will calculate the time based on when he start the sequence to when he stop the sequence? Thanks |
Help with calculating time
See your other post where I included a screencap.
In my test file I'm getting the *correct* results: Should be: 55 (5+10+10+30) 65 (10+30+25) 140 (5+10+10+30+25+60) -- Biff Microsoft Excel MVP "Cam" wrote in message ... Valko, I got the results, but the calculation is off little. The calculated cell are in column G of sheet1. The last info is what it should be calculated values. For example, sheet1 row 2 should be equal 55 cause it see start (seq) to stop (seq) is 100 to 400 so it should sum all value from row 2 to 5 (5+10+10+30) in column D from Ref sheet. Sheet1 - Data sheet A B C D E F G ID date part# side start stop Cal Time Should be: 1 1/8/08 1360-1 1st 100 400 50 55 (5+10+10+30) 2 1/10/08 1360-1 1st 300 500 125 65 (10+30+25) 3 1/10/08 1360-1 1st 100 600 135 140 (5+10+10+30+25+60) Ref - Ref sheet A B C D part# side seq time 1360-1 1st 100 5 1360-1 1st 200 10 1360-1 1st 300 10 1360-1 1st 400 30 1360-1 1st 500 25 1360-1 1st 600 60 1560-3 2nd 100 10 "T. Valko" wrote: You'll have to post a sample of data and show where the formula is returning a 0. -- Biff Microsoft Excel MVP "Cam" wrote in message ... Hi Valko, I tried the formula you suggested, but it returns "0" where it shouldn't be. "T. Valko" wrote: Try this: With this data on Sheet2 in the range A2:D8 1360-1...1st...100...5 1360-1...1st...200...10 1360-1...1st...300...10 1360-1...1st...400...30 1360-1...1st...500...15 1360-1...1st...600...60 1560-3...2nd...100...10 With this data on Sheet3 in the range A2:F2 001...1/8/08...1360-1...1st...100...500 Enter this formula on Sheet3 G2: =SUMPRODUCT(--(Sheet2!A$2:A$8=C2),--(Sheet2!B$2:B$8=D2),--(Sheet2!C$2:C$8=E2),--(Sheet2!C$2:C$8<=F2),Sheet2!D$2:D$8) -- Biff Microsoft Excel MVP "Cam" wrote in message ... Hello, I have an Excel to track performance. Sheet1 is performance table where user input their ID, date, part#, side, start sequence & stop sequence daily. Then on a reference sheet2 with field: part#, side, sequence#, time (calculated). What I would like to calculate is look at the start & stop sequence based on the part# & side then calculate how long (time) the operator spend on the job (part#). Thanks Example: Sheet1 - User input data ID date part# side start seq stop seq 001 1/8/08 1360-1 1st 100 500 002 1/10/08 1560-3 2nd 600 1000 Sheet2 - Reference part# side start seq time (min) 1360-1 1st 100 5 1360-1 1st 200 10 1360-1 1st 300 10 1360-1 1st 400 30 1360-1 1st 500 15 1360-1 1st 600 60 1560-3 2nd 100 10 .......... Result Sheet3 ID date part# side start seq stop seq Total time (calculated) 001 1/8/08 1360-1 1st 100 500 70 How should i go about linking the two sheets so it will calculate the time based on when he start the sequence to when he stop the sequence? Thanks |
| All times are GMT +1. The time now is 11:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com