Overlapping/inclusive date time calculation

[mattk918]

Hi. Just wrote a long(ish) help request and lost it when I tried to post!
My problem that I can’t find a solution to is as follows

I have a large amount of fault data from multiple sites. In particular I am looking at fault time (Finish time-Start Time) in HH:MM. Base upon the specific fault code (not shown in the table below) a weighting is applied to the fault time of either 0, 0.33, 0.67 or 1.

I started by using the method from this link in column S with the following equation

=IF(C3=C4,IF(OR(N4<J3,N3<J4),"Do not overlap","Overlap"),"New Site ID")

Check if two ranges of dates overlap [Excel Formulas] | Chandoo.org - Learn Microsoft Excel Online

However this only works assuming the overlapping faults are consecutive in the list

I then tried in column V using the following

=SUMPRODUCT(N(C$3:C$5000=C3),N(J$3:J$5000>=J3),N(N$3:N$5000<=N3))>1

However this doesn’t capture faults where there is an overlap, only those where one fault time is completely within the other.

To add complication the highest weighting fault needs to take priority (e.g. 1. If we have 2 faults both of 30 minutes, one with a weighting of 0.67 and the other 0.33 and they overlap by 10 minutes my fault time in Column P should be 30 mins for the 0.67 and 20 mins for the 0.33. ) (e.g.2. If fault 1 is 1 hour weighting 0.33 and fault 2 weighting 0.67 takes place entirely within the period of fault 1 for 20 mins, then my times would be 40 mins for fault 1 and 20 mins for fault 2) Clear as mud so far???? It could be that there are multiple faults all at one site that all overlap, not just 2.

Basically my query is can this be done just using formulas within excel or do I need to go the VBA route (have dabbled before but very rusty and wouldn’t really know where to start). Hopefully there is a really simple way to do this (but somehow I doubt it!!

Any help gratefully received

Cheers
Matt

Sample from data

Column C	Column I	Column J	Column N	Column O	Column P	Column Q	Column R	Column S	Column T	Column S	Column V
	Start (Actual)	Start (Contractor)	Finish	Reported	Contractor Informed	Reported	Contractor Informed
Site ID (Auth)	Reported	Contractor Informed	Close	Fault Time (Hours)	Fault Time (Hours)	Fault Code Weighting	Fault Time (Hours)	Fault Time (Hours)		Date Overlap Check
1	04/05/2016 11:01	04/05/2016 11:11	30/06/2016 10:45	1367:44:00	1367:33:49	0.333333333	455:54:40	455:51:16		New Site ID	FALSE
2	29/09/2016 10:41	29/09/2016 10:41	29/09/2016 12:45	2:04:00	2:04:00	0.333333333	0:41:20	0:41:20		Do not overlap	FALSE
2	27/07/2016 08:20	27/07/2016 08:28	02/08/2016 12:30	148:10:00	148:01:54	0.333333333	49:23:20	49:20:38		Do not overlap	FALSE
2	24/07/2016 08:20	24/07/2016 08:25	24/07/2016 10:00	1:40:00	1:34:54	1	1:40:00	1:34:54		Do not overlap	FALSE
2	27/06/2016 10:11	27/06/2016 10:11	28/06/2016 12:45	26:34:00	26:33:24	0.666666667	17:42:40	17:42:16		Do not overlap	FALSE
2	23/06/2016 14:47	23/06/2016 14:49	24/06/2016 11:00	20:12:39	20:10:39	0.666666667	13:28:26	13:27:06		New Site ID	FALSE
3	06/07/2016 20:45	06/07/2016 20:51	06/07/2016 21:00	0:14:10	0:08:59	0	0:00:00	0:00:00		Overlap	FALSE
3	06/07/2016 20:45	06/07/2016 20:50	06/07/2016 21:00	0:14:10	0:09:20	1	0:14:10	0:09:20		Do not overlap	TRUE
3	29/06/2016 09:54	29/06/2016 09:56	04/07/2016 17:05	127:11:00	127:08:01	0.333333333	42:23:40	42:22:40		Do not overlap	FALSE
3	23/06/2016 10:04	23/06/2016 10:06	23/06/2016 10:08	0:04:00	0:01:16	0.333333333	0:01:20	0:00:25		Do not overlap	FALSE
3	22/06/2016 11:45	22/06/2016 11:45	23/06/2016 10:00	22:15:00	22:14:01	0.333333333	7:25:00	7:24:40		Overlap	TRUE
3	22/06/2016 20:46	22/06/2016 20:48	23/06/2016 07:20	10:33:17	10:31:58	1	10:33:17	10:31:58		New Site ID	FALSE
4	22/06/2016 20:47	22/06/2016 20:48	23/06/2016 07:20	10:32:16	10:31:32	0	0:00:00	0:00:00		New Site ID	FALSE
5	26/01/2017 08:29	26/01/2017 08:29	26/01/2017 11:15	2:45:34	2:45:34	0.333333333	0:55:11	0:55:11		Do not overlap	FALSE
5	24/08/2016 20:45	24/08/2016 20:46	24/08/2016 21:45	0:59:17	0:58:18	1	0:59:17	0:58:18		Do not overlap	FALSE

<tbody>
</tbody>

 

[konew1]

I suggest you post a small sample of where you are up to. I don't think it can be done neatly with formula. Possibly a VBA solution will do it, but I don't write code. Someone else might help.
The only way I can see a formula solution will involve an enormous number of helper columns (24*60=1440). You would need a column for each minute time slot. In every time slot column have a formula to look up the start and end times of the fault. If the fault existed in that minute then enter the weighting code. Then have a summary panel listing each site and finding the maximum weighting code in each minute. Then summarise further by counting the number of minutes of each weighting code at each site. I am confident that this will work, but almost certain there are some better ways.

 

[mattk918]

Actually I think I'd need 1051200 columns because there is 2 years worth of data and some faults are long standing.

Simple ish vba solution anyone?

 

[MisterBates]

Assuming we can overcome the overlapping dates/times and fault priorities, how do you plan to use the result?

 

[mattk918]

It is an exercise to get rid of the double (treble, quadruple etc) accounting of fault time per site. The "corrected" fault time for each site would then be used to calculate site % availability using elapsed real time over the period in question. (e.g. If I have 3 faults, all 1 hour long, and all completely overlapping each other, for a time period of 3 hours, then my fault time should be 1 hour so availability = 100(3-1)/3 = 66.6% availability. If I just sum the fault time I'll end up with 0% availability)

 

[MisterBates]

OK, I've thought about what you're trying to do, and a solution could be as follows:
1) Sort the data by site, by decreasing Fault Code Weight then by increasing start time and increasing end time
2) For each row, check if there is an overlap "higher up" in the sorted table - if there is, subtract the overlap from the current row (as that overlap should be counted as part of the higher-up row)
3) Repeat step (2) for each higher-up row that overlaps the current row.

Did I understand (and restate) the problem correctly?

The issue as I see it is that implementing steps 2 and 3 will (on the face of it) require a matrix formula, and with large datasets this might take a long time to calculate. There may be a sumproduct() solution ... I'll see if I can find one.

Worst comes to worst, it should be possible to process the table in VBA (doing the sorting and the evaluation using collections) ...

 

[mattk918]

Think you've understood the problem. Not sure if your solution of sorting will capture the 4 possible modes of overlap though?

A-----------------B
........X-----Y


.......A-----B
X------------------Y


A--------------B
.............X---------Y


...........A--------B
X-----------Y

(where A and B are start time and end time of one fault and X and Y are start time and end time of another fault, bearing in mind there may be many faults fully and partially overlapping each other at any one time?????)

 

[MisterBates]

I think it'll capture it, as conceptually:
* The first record for a site will be at the highest priority for the site so should be counted in full
* Second (and subsequent) records for a site will be in decreasing order of priority, so need to be checked with all earlier records for the site to take out overlaps, and whatever is left after "de-overlapping" should be counted at that priority

The difficulty is in finding a formula that can do the de-overlapping. I just tried with sumproduct() and the issue there is that while I can get a set of results showing the amount of overlap with each row above the current, I can't think of a way for sumproduct to know about multiple overlaps.

Looks like VBA is the way to go ... I'll see what I can code up.

 

[MisterBates]

Quick question - is the weight always a number from 0 to 1? If so, it makes the VBA sort easier (and avoids a need to sort the actual data)

The VBA I'm considering is pseudo-coded as follows:
1) Read data into scripting.dictionary where each dictionary entry has the site ID as key and a sub-dictionary as the value
(the sub-dictionary has weight/start/end/row# combination as the key and a variant array of values from the input as the dictionary value)
2) For each main dictionary entry (per site), use cpearson's SortDictionary to get a sorted sub-dictionary
(SortDictionary is at: Sorting A Collection Or Dictionary)
3) Process the sorted sub-dictionary to produce an "overlap" array
(the overlap array lists each time period overlapping the row being processed where each entry is "de-duplicated" so that entries are only added for a new overlap)
4) Process the overlap array to calculate the total time for the current entry that needs to be subtracted (due to earlier rows already covering the time period(s))
5) Output the "overlap hours" and a text field describing which rows/times have been used to calculate the overlapped hours - output into additional columns in the row that was processed

At the moment, I can't think of an easier solution, as the above can be stated in english as "calculate overlap hours as the hours from higher/same-weighted rows that overlap some/all of the time between start/end in the current row".

 

[mattk918]

Yes to the weighting query.

Sounds complicated, but then again the crazy spreadsheet I have going on at the moment is pretty complicated... started to get lost there are so many nested Ifs, and so much error checking to cover when people have inputted finish dates before start etc!

Thanks for looking at this for me

Matt

 

[MisterBates]

Matt,

I have a workbook ready for you containing a couple of classes and a couple of modules. I can't upload it so will need to email - can you drop me a private message with an email address that I can mail the workbook to?

Steve (MisterBates)