A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Overlapping time ranges



 
 
Thread Tools Display Modes
  #1  
Old June 14th, 2004, 06:24 PM
jsollman
external usenet poster
 
Posts: n/a
Default 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  
Old June 14th, 2004, 07:21 PM
Charlie
external usenet poster
 
Posts: n/a
Default 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  
Old June 15th, 2004, 10:46 PM
Anders Silven
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.