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.

Start	End	Days	Hours
03/01/2018 18:30:00	04/01/2018 02:42:00	0.342	8.20

<tbody>
</tbody>

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.

Hours Impacted Range	Total Hours
06:00 - 11:59	0
12:00 - 14:59	0
15:00 - 17:59	0
18:00 - 22:59	4.5
23:00 - 05:59	3.7

<tbody>
</tbody>

 

[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.