VBA to separate overlapping times

75 pts.
Tags:
Excel 2007
Timestamp
VB codes
VBA
I am stuck trying to seperate overlapping times. The situation is that I have 5 devices requiring x amount of material at the differing durations. What I need to do is seperate the durations and add the demands together for the new duration, but there can be as many as 5 overlapping demands; here is an example of the times and demands

              Time             Demand

4:31:26 to 4:35:50           4

4:31:26 to 4:35:38           2

4:32:10 to 4:33:20           8

4:33:15 to 4:34:10           9

I need to create individual times and total demands for those times for thousands of lines of time stamps.



Software/Hardware used:
Excel 2007, VBA coding

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 13  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Can you show us an example of the results you would want from the example data you provided ? Thanks,
    69,175 pointsBadges:
    report
  • Jace
    I will give an example of what I am starting with and trying to achieve. Code Start time End time Demand BEC405 5:25:53 5:27:05 1 BEC405 5:20:53 5:21:05 1 BEC405 5:17:17 5:21:05 1 BEC405 5:17:17 5:20:41 1 BEC405 5:16:18 5:22:42 1 BEC405 5:15:18 5:15:42 1 Want to be in the format shown below Code Start time End time Demand BEC405 5:25:53 5:27:05 1 BEC405 5:16:18 5:17:17 1 BEC405 5:17:17 5:20:41 3 BEC405 5:20:41 5:20:53 2 BEC405 5:20:53 5:21:05 2 BEC405 5:21:05 5:22:42 1 BEC405 5:15:18 5:15:42 1 But for thousands of lines of code that may or may not be overlapping
    75 pointsBadges:
    report
  • carlosdl
    Sorry, I'm still not sure to understand. In this line:
    BEC405 5:17:17 5:20:41 3
    Where does the '3' (demand) come from ? What's the logic behind those results ? This is the original data:
    	Code	Start time	End time	Demand
    1	BEC405	5:25:53		5:27:05		1
    2	BEC405	5:20:53		5:21:05		1
    3	BEC405	5:17:17		5:21:05		1
    4	BEC405	5:17:17		5:20:41		1
    5	BEC405	5:16:18		5:22:42		1
    6	BEC405	5:15:18		5:15:42		1
    and I can see that the interval '5:17:17 - 5:20:41' is included or overlaps with 3 records (3,4,5), but this other interval '5:20:53 - 5:21:05' is also included or overlaps with 3 records (2,3,5) but in the results it has a demand of '2'. OTOH, I'm not sure what this mean: "but there can be as many as 5 overlapping demands" Does that mean that this have to validated and some different action must be taken if there are more than 5 overlapping demands ? This looks complex indeed.
    69,175 pointsBadges:
    report
  • Jace
    Yes it is complex, I am illustrating that the "code" is BEC405 for the device the that has a "demand" of 1 gallon per minute during the period from the "start time" to the "end time". The demands overlap because there are multiple demands by the same device. I cannot have the times overlapping so I am summong the demands and creating a new "start time" and "end time" for that demand.
    75 pointsBadges:
    report
  • Jace
    There can be 5 demands overlapping a time period. My example illustrates 3 times that are overlapping. Thnaks for the brain power.
    75 pointsBadges:
    report
  • TomLiotta
    Further, this interval -- 5:16:18 5:22:42 -- totally overlaps three other intervals. Why wasn't it given a value of "4" while simply dropping the other three shorter intervals? Carlosdl asked why ‘5:20:53 - 5:21:05′ was assigned demand value of (2) rather than (3). I agree. What reasoning groups ‘5:20:53 - 5:21:05′ one way, '5:16:18 - 5:22:42' a second way, and '5:17:17 - 5:20:41' yet a third way? There are other questions, e.g., why are there 6 Demand indicators at first, but 11 after the reformatting? (I used "indicators" because I have no good idea what they actually represent.) I can see how you came up with the final seven rows from the original six and it seems correct, but the meaning isn't clear. Does the 'Demand' element have any meaning? I haven't found any "summing" that matches the results that you show. Tom
    125,585 pointsBadges:
    report
  • Jace
    The times are sorted relative to the name in the code then the start time and then the end time. The demands represent a certian flow rate per hour of solution into a tank from a single line with multiple outlets, each outlet may open at a seperate time depending on the demand by the tank, as such the flow rate through each line is represented as "1". I am sorry I am unsure what this question is asking. "There are other questions, e.g., why are there 6 Demand indicators at first, but 11 after the reformatting? (I used “indicators” because I have no good idea what they actually represent.) I can see how you came up with the final seven rows from the original six and it seems correct, but the meaning isn’t clear. Does the ‘Demand’ element have any meaning? I haven’t found any “summing” that matches the results that you show." and where the number 11 comes from is just confusing
    75 pointsBadges:
    report
  • TomLiotta
    ...where the number 11 comes from is just confusing Confusing because I used the number 11? Or confusing because you're not sure how you came up with your values?
    Code Start time End time Demand
    
    BEC405 5:25:53 5:27:05 1
    BEC405 5:20:53 5:21:05 1
    BEC405 5:17:17 5:21:05 1
    BEC405 5:17:17 5:20:41 1
    BEC405 5:16:18 5:22:42 1
    BEC405 5:15:18 5:15:42 1
                          ---
                           6
    
    Code Start time End time Demand
    
    BEC405 5:25:53 5:27:05 1
    BEC405 5:16:18 5:17:17 1
    BEC405 5:17:17 5:20:41 3
    BEC405 5:20:41 5:20:53 2
    BEC405 5:20:53 5:21:05 2
    BEC405 5:21:05 5:22:42 1
    BEC405 5:15:18 5:15:42 1
                          ---
                          11
    I was wondering how the new 'Demand' values were derived. The times make sense to me, but I don't see the meaning in them other than (mostly) continuous spans of time. Without some grasp of meaning, it's tough to know when an algorithm will apply to all combination. Tom
    125,585 pointsBadges:
    report
  • Jace
    I see where you are gathering 11 from now, the demands should not be summed as a total, respectfully this is not what I am getting at. Yes the time gaps are small but imact the head loss significantly in the system, those short pasuses are responsible for the system crashing and burning, thanks trying to wrap your head around what I am asking.
    75 pointsBadges:
    report
  • TomLiotta
    The situation is that I have 5 devices... ...and... ...there can be as many as 5 overlapping demands Is "5 devices" the specific reason for 5 possible overlapping demands? Or are those simply coincidental? From the question: I need to create ... total demands... But from your latest comment: ...the demands should not be summed as a total Then what should be done with them? I'm still missing something that should clear it up. those short pasuses are responsible for the system crashing and burning Is it valid to have a Demand of zero for any time span? Tom Tom
    125,585 pointsBadges:
    report
  • carlosdl
    I was going to ask more questions yesterday too, but I decided not to do it when the numbers started making sense to me. However I still think that ‘5:20:53 - 5:21:05′ should have a demand of (3) in the result. If the original demand column means gallons per minute, we can use the interval duration to calculate an absolute demand. This way there is no difference in the total demand when compared to the results: Original data:
    Code	Start time	End time	Demand/min	Duration	Demand
    BEC405	5:15:18		5:15:42		1		0:00:24		0.4
    BEC405	5:16:18		5:22:42		1		0:06:24		6.4
    BEC405	5:17:17		5:20:41		1		0:03:24		3.4
    BEC405	5:17:17		5:21:05		1		0:03:48		3.8
    BEC405	5:20:53		5:21:05		1		0:00:12		0.2
    BEC405	5:25:53		5:27:05		1		0:01:12		1.2
    TOTAL									15.4
    Result:
    Code	Start time	End time	Demand/min	Duration	Demand
    BEC405	5:15:18		5:15:42		1		0:00:24		0.400
    BEC405	5:16:18		5:17:17		1		0:00:59		0.983
    BEC405	5:17:17		5:20:41		3		0:03:24		10.200
    BEC405	5:20:41		5:20:53		2		0:00:12		0.400
    BEC405	5:20:53		5:21:05		3		0:00:12		0.600
    BEC405	5:21:05		5:22:42		1		0:01:37		1.617
    BEC405	5:25:53		5:27:05		1		0:01:12		1.200
    TOTAL									15.4
    If this reasoning is correct, now we just need to find the time to design the algorithm and code it :-)
    69,175 pointsBadges:
    report
  • Jace
    You are correct that should have been 3 not 2 thanks. I have been trying to write if and else statements with very little success, just cant investigate far enough down the series of data to have success. I am trying to make several itterations of a if, ad, else statement to solve this. If anyone has a suggested method I am very open to help, the faster the methods calculation the better there is a lot of data point 55000 a day. Thank you.
    75 pointsBadges:
    report
  • Jace
    The situation is that I have 5 devices… …and… …there can be as many as 5 overlapping demands Is “5 devices” the specific reason for 5 possible overlapping demands? Or are those simply coincidental? The easiest way to think of this is there is a pipe with with 5 nosels on it and the more of the nosels I am using increases the demand on the the pipe. Therefore if 3 nossels are used at the same time the demand is three on the pipe. Demands happen at any time the device with the code requires it. From the question: I need to create … total demands… But from your latest comment: …the demands should not be summed as a total Then what should be done with them? Total demands was in reference to; if two, three or four time periods overlap then the demands would sum for two three or four demands, not that I had 11 demands over a time period of 12 minutes. I’m still missing something that should clear it up. those short pasuses are responsible for the system crashing and burning Is it valid to have a Demand of zero for any time span? a time period of zero are not important, if times share the same second that is fine as a start time and an end time.
    75 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following