If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Overlapping time ranges
I am trying to create a formula to show the total time a factory line
was down during a given shift. my problem is the overlapping times. If i just sum the times the line was stopped for an alarm I end up with well over 8 hours in an 8 hour shift. the table below shows a piece of the data I get in from the line monitor. the line was down from 05:29:58 PM untill 06:01:39 PM or @ 30mins. if you just sum the alarm times you get @ 80mins. so you can see my delema. This has got to be a tough one or I'm just not seeing it I'm on day 2 with this problem with no avail. Please Help... 6/10/2004 04:38:10 PM 00:14:41 6/10/2004 04:52:51 PM Spraybooth Manlift #3 Not Home 6/10/2004 04:39:05 PM 00:06:04 6/10/2004 04:45:09 PM Sand Area Operator Platform #1 Not Home 6/10/2004 05:29:58 PM 00:03:59 6/10/2004 05:33:57 PM Spraybooth Manlift #1 Not Home 6/10/2004 05:29:58 PM 00:02:36 6/10/2004 05:32:34 PM Spraybooth Manlift #2 Not Home 6/10/2004 05:29:58 PM 00:02:20 6/10/2004 05:32:18 PM Spraybooth Manlift #4 Not Home 6/10/2004 05:29:58 PM 00:04:54 6/10/2004 05:34:52 PM Washbooth Manlifts Not Home 6/10/2004 05:29:58 PM 00:09:35 6/10/2004 05:39:33 PM Mask Area Run/Stop 5 6/10/2004 05:29:58 PM 00:21:20 6/10/2004 05:51:18 PM Conveyor Part Not Cleared at End of the L 6/10/2004 05:30:01 PM 00:04:40 6/10/2004 05:34:41 PM Spraybooth Run/Stop 7 6/10/2004 05:30:13 PM 00:15:46 6/10/2004 05:45:59 PM Sand Area Run/Stop 2 6/10/2004 05:47:31 PM 00:14:08 6/10/2004 06:01:39 PM Dry Off Oven Burner Controller Fault 6/10/2004 06:26:34 PM 00:03:55 6/10/2004 06:30:29 PM Sand Area Operator Platform #1 Not Home 6/10/2004 06:26:34 PM 00:01:15 6/10/2004 06:27:49 PM Spraybooth Manlift #1 Not Home Thanks Jeffery Advanced Tech Services --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Overlapping time ranges
One though, the down time should be formatted as h:mm:ss,
this can be found under Format Cell Custom. When I use this format and SUM the down time I get 1:45:13. Charlie O'Neill -----Original Message----- I am trying to create a formula to show the total time a factory line was down during a given shift. my problem is the overlapping times. If i just sum the times the line was stopped for an alarm I end up with well over 8 hours in an 8 hour shift. the table below shows a piece of the data I get in from the line monitor. the line was down from 05:29:58 PM untill 06:01:39 PM or @ 30mins. if you just sum the alarm times you get @ 80mins. so you can see my delema. This has got to be a tough one or I'm just not seeing it I'm on day 2 with this problem with no avail. Please Help... 6/10/2004 04:38:10 PM 00:14:41 6/10/2004 04:52:51 PM Spraybooth Manlift #3 Not Home 6/10/2004 04:39:05 PM 00:06:04 6/10/2004 04:45:09 PM Sand Area Operator Platform #1 Not Home 6/10/2004 05:29:58 PM 00:03:59 6/10/2004 05:33:57 PM Spraybooth Manlift #1 Not Home 6/10/2004 05:29:58 PM 00:02:36 6/10/2004 05:32:34 PM Spraybooth Manlift #2 Not Home 6/10/2004 05:29:58 PM 00:02:20 6/10/2004 05:32:18 PM Spraybooth Manlift #4 Not Home 6/10/2004 05:29:58 PM 00:04:54 6/10/2004 05:34:52 PM Washbooth Manlifts Not Home 6/10/2004 05:29:58 PM 00:09:35 6/10/2004 05:39:33 PM Mask Area Run/Stop 5 6/10/2004 05:29:58 PM 00:21:20 6/10/2004 05:51:18 PM Conveyor Part Not Cleared at End of the L 6/10/2004 05:30:01 PM 00:04:40 6/10/2004 05:34:41 PM Spraybooth Run/Stop 7 6/10/2004 05:30:13 PM 00:15:46 6/10/2004 05:45:59 PM Sand Area Run/Stop 2 6/10/2004 05:47:31 PM 00:14:08 6/10/2004 06:01:39 PM Dry Off Oven Burner Controller Fault 6/10/2004 06:26:34 PM 00:03:55 6/10/2004 06:30:29 PM Sand Area Operator Platform #1 Not Home 6/10/2004 06:26:34 PM 00:01:15 6/10/2004 06:27:49 PM Spraybooth Manlift #1 Not Home Thanks Jeffery Advanced Tech Services --- Message posted from http://www.ExcelForum.com/ . |
#3
|
|||
|
|||
Overlapping time ranges
Hi Jeffery,
I'm quite sure you can't do this without VBA, are you ready for that? I have been playing a bit with the sample data and may have a solution that works. At least it does on the sample data, although it requires some rearranging of the worksheet. If you like, you can mail me a workbook with some more data so that I can test the macro on a larger scale, I suppose a couple of hundred lines will do. Nice problem btw Regards, Anders Silven email: temp1 at silven dot se |
Thread Tools | |
Display Modes | |
|
|