| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating Regular time, overtime and double time | Excel Worksheet Functions | |||
| Calculating time and negative time | Excel Worksheet Functions | |||
| calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
| Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
| Calculating effective time from start/end date+time | Excel Worksheet Functions | |||
Linear Mode
