How can split an outage time duration into different segments / ranges

[edwardj3]

I am monitoring service outages using excel 2007. I have a start and and end time as shown in the example below. Start and end time are in dd/mm/yyyy hh:mm:ss format.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Days[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]03/01/2018 18:30:00[/TD]
[TD]04/01/2018 02:42:00[/TD]
[TD]0.342[/TD]
[TD]8.20[/TD]
[/TR]
</tbody>[/TABLE]

Days is worked out using the following formula =IF(H31>G31,H31-G31, 1-G31+H31)
Hours worked out using the following formula =M31*24

Whilst the above provides the total outage duration is it possible to split the outage duration into segments / time ranges using a formula/formulas or some code? So for the example above the result would be as below. The formula/ code must take into account that an outage could span different days.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Hours Impacted Range[/TD]
[TD]Total Hours[/TD]
[/TR]
[TR]
[TD]06:00 - 11:59[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12:00 - 14:59[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15:00 - 17:59[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18:00 - 22:59[/TD]
[TD]4.5[/TD]
[/TR]
[TR]
[TD]23:00 - 05:59[/TD]
[TD]3.7
[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

See if this works for you:

Excel 2010
	A	B	C	D
1	Start	End	Days	Hours
2	1/3/2018 18:30	1/4/2018 2:42	0.342	8.2
3
4
5	Hours Impacted Start	Hours Impacted End	Total Hours
6	1/3/2018 6:00	1/3/2018 12:00	0
7	1/3/2018 12:00	1/3/2018 15:00	0
8	1/3/2018 15:00	1/3/2018 18:00	0
9	1/3/2018 18:00	1/3/2018 23:00	4.5
10	1/3/2018 23:00	1/4/2018 5:59	3.7
11
Sheet1

Cell Formulas
Range	Formula
C2	=IF(B2>A2,B2-A2, 1-A2+B2)
C6	=MAX(0,MIN(B6,$B$2)-MAX($A$2,A6))/($B$2-$A $2)*$D$2
C7	=MAX(0,MIN(B7,$B$2)-MAX($A$2,A7))/($B$2-$A$2)*$D$2
C8	=MAX(0,MIN(B8,$B$2)-MAX($A$2,A8))/($B$2-$A$2)*$D$2
C9	=MAX(0,MIN(B9,$B$2)-MAX($A$2,A9))/($B$2-$A$2)*$D$2
C10	=MAX(0,M IN(B10,$B$2)-MAX($A$2,A10))/($B$2-$A$2)*$D$2
D2	=C2*24

 

[edwardj3]

Your solution works perfectly VBA Geek. Thanks for responding so quickly. If you get an opportunity could you provide an explanation so I fully understand what the formula is actually doing. Thanks again.