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	Reported
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>
[TD="colspan: 2"] Contractor Informed
[/TD]

[TD="colspan: 2"] Contractor Informed
[/TD]

</tbody>

 

[konew1]

One of your formulas is truncated. You need repost. For some crazy reason sometimes this forum will truncate formula with < or > unless there 1s a space after the < or >.

Instead of =SUMPRODUCT(N(C$3:C$5000=C3),N(J$3:J$5000>=J3),N(N$3:N$5000<=N3))>1 don't you mean
=SUMPRODUCT(N(C$3:C$5000=C3),N(J$3:J$5000>=J3),N(J$3:J$5000<=N3))>1

 

[mattk918]

One of your formulas is truncated. You need repost. For some crazy reason sometimes this forum will truncate formula with < or > unless there 1s a space after the < or >.

Thanks..... Full formula is

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

Instead of =SUMPRODUCT(N(C$3:C$5000=C3),N(J$3:J$5000>=J3),N(N$3:N$5000<=N3))>1 don't you mean
=SUMPRODUCT(N(C$3:C$5000=C3),N(J$3:J$5000>=J3),N(J$3:J$5000<=N3))>1

Didn't think so, but maybe!?!? Been working on this for way too many hours and gone into brain meltdown so you could be right. However, when I tried your version it didn't work for my test example?

 

[konew1]

Hi
My suggestion doesn't find the last instance of overlap, so will need correction. I have been thinking how to solve your requirement to allocate weighting to the overlap periods, but without success.

 

[mattk918]

Thanks for giving it some thought! I have been lying awake overnight trying to think of another way of achieving what I want. .... with no luck so far

 

[mattk918]

Any geniuses out there able to give me any pointers on this problem? Preferably a formula based solution (because I have more of being able to understand it! !!) But if it needs to be vba so be it.

Is there any other info I need to supply?

Thanks in advance
Matt

 

[mattk918]

.

 

[mattk918]

Been trying to develop the formula in my 1st post (shown below) to somehow include an OR statement so it would also capture the over laps where only the start or end of a fault is within the time period of another fault rather than the whole fault. Any advice on the syntax or whether it can even be made to work?

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

 

[mattk918]

Sorry keep ending up double posting.

 

[mattk918]

Been trying to develop the formula in my 1st post (shown below) to somehow include an OR statement so it would also capture the over laps where only the start or end of a fault is within the time period of another fault rather than the whole fault. Any advice on the syntax or whether it can even be made to work?

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

Got 4 permutations of this formula now to capture all variations of overlap so I have four cells with true and false in them..
If any of the 4 are true I flag as overlap. Not sure how much it helps.......Still can't work out how to correctly weight them though. Is there no-one of there who can help at all?