Overlapping time ranges

[jsollman]

I am trying to create a formula to show the total time a factory lin
was down during a given shift.
my problem is the overlapping times. If i just sum the times the lin
was stopped for an alarm I end
up with well over 8 hours in an 8 hour shift. the table below shows
piece of the data I get in
from the line monitor. the line was down from 05:29:58 PM until
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 Operato
Platform #1 Not Home
6/10/2004 05:29:58 PM 00:03:59 6/10/2004 05:33:57 PM Spraybooth Manlif
#1 Not Home
6/10/2004 05:29:58 PM 00:02:36 6/10/2004 05:32:34 PM Spraybooth Manlif
#2 Not Home
6/10/2004 05:29:58 PM 00:02:20 6/10/2004 05:32:18 PM Spraybooth Manlif
#4 Not Home
6/10/2004 05:29:58 PM 00:04:54 6/10/2004 05:34:52 PM Washbooth Manlift
Not Home
6/10/2004 05:29:58 PM 00:09:35 6/10/2004 05:39:33 PM Mask Area Run/Sto
5
6/10/2004 05:29:58 PM 00:21:20 6/10/2004 05:51:18 PM Conveyor Part No
Cleared at End of the L
6/10/2004 05:30:01 PM 00:04:40 6/10/2004 05:34:4
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/Sto
2
6/10/2004 05:47:31 PM 00:14:08 6/10/2004 06:01:39 PM Dry Of
Oven Burner Controller Fault
6/10/2004 06:26:34 PM 00:03:55 6/10/2004 06:30:29 PM Sand Area Operato
Platform #1 Not Home
6/10/2004 06:26:34 PM 00:01:15 6/10/2004 06:27:49 PM Spraybooth Manlif
#1 Not Home

Thanks
Jeffery
Advanced Tech Service

 

[Charlie]

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

 

[Anders Silven]

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