Superhuman Excel Knowledge required to solve this??

[nisbad]

I have been considering myself as an excel expert.. only till i came across this problem..

I have been working on the Network Availability of Cell Towers, while i have ways to calculate the overall Network Availability of the tower. i now want to know the hourly network availability in each city.

Data Source has

Start Time of Fault & End Time of Fault.

So if a Cell Tower is Down from Say 0:10 Hrs to 06:10 Hrs the excel formula should have 50 minutes in Hour 1, 60 Mins in Hour 2,3,4,5 and 10 mins in Hour 6.

Sample data pasted below...

EventStartDate	StartTime	EndTime	Hour 1	Hour 2	Hour 3	Hour 4	Hour 5	Hour 6
11/1/2013	0:00	0:15	15	-	-	-	-	-
11/1/2013	0:00	0:20	20	-	-	-	-	-
11/1/2013	0:00	0:22	22	-	-	-	-	-
11/1/2013	0:00	0:34	34	-	-	-	-	-
11/1/2013	0:00	1:15	60	15	-	-	-	-
11/1/2013	0:00	5:14	60	60	60	60	60	14

<colgroup><col><col span="2"><col span="6"></colgroup><tbody>
</tbody>

Help will be appreciated...

 

[bertie]

Hi and welcome to the forum.
It ain't pretty but here is one possible solution.
Note how the TimeValue increases as the column increases.

Excel 2007
	A	B	C	D	E	F	G	H	I
1	EventStartDate	StartTime	EndTime	Hour 1	Hour 2	Hour 3	Hour 4	Hour 5	Hour 6
2	11/01/2013	00:00	05:14	50	60	60	60	60	14
Sheet1

Cell Formulas
Range	Formula
D2	=IF($C2-$B2"01:00:00"),($C2-$B2)*1440,50)
E2	=IF($C2-$B2"02:00:00"),($C2-$B2-TIMEVALUE("01:00:00"))*1440,60)
F2	=IF($C2-$B2"03:00:00"),($C2-$B2-TIMEVALUE("02:00:00"))*1440,60)
G2	=IF($C2-$B2"04:00:00"),($C2-$B2-TIMEVALUE("03:00:00"))*1440,60)
H2	=IF($C2-$B2"05:00:00"),($C2-$B2-TIMEVALUE("04:00:00"))*1440,60)
I2	=IF($C2-$B2"06:00:00"),($C2-$B2-TIMEVALUE("05:00:00"))*1440,60)

 

[Snakehips]

nisbad,

Welcome to MrExcel.

Here is another option using a formula that is good to copy down and across.
I assumed that not all start times are 00:00 and that eg an event starting say 01:00 would have nothing in Hour 1??

Excel 2007
	A	B	C	D	E	F	G	H	I
1	EventStartDate	StartTime	EndTime	Hour 1	Hour 2	Hour 3	Hour 4	Hour 5	Hour 6
2	11/01/2013	00:00	00:15	15	-	-	-	-	-
3	11/01/2013	00:00	00:20	20	-	-	-	-	-
4	11/01/2013	00:00	00:22	22	-	-	-	-	-
5	11/01/2013	00:00	00:34	34	-	-	-	-	-
6	11/01/2013	00:00	01:15	60	15	-	-	-	-
7	11/01/2013	00:00	05:14	60	60	60	60	60	14
8	11/01/2013	01:10	05:14	-	60	60	60	60	4
Sheet2

Cell Formulas
Range	Formula
D2	=IF(HOUR($C2)=COLUMNS($B2:B2)-1,MINUTE($C2-$B2),IF(AND(HOUR($C2)>=COLUMNS($B2:B2),HOUR($B2)$B2:B2)),60,"-"))

Hope that helps.

 

[nisbad]

Hi Bertie & Snakehips,

Must say you guys are geniuses and I really appreciate the quick response.. but still stuck.. with following error (please refer to the last col "Comment" for my issues

EventStartDate	StartTime	EndTime	Hour 1	Hour 2	Hour 3	Hour 4	Hour 5	Hour 6	Hour 7	Comment
2/1/2014	0:00	0:03	3	-	-	-	-	-	-	Correct
2/1/2014	0:00	0:07	7	-	-	-	-	-	-	Correct
2/1/2014	0:37	2:07	60	60	30	-	-	-	-	Hour 1 Should have 23 which is 60-37 and hour 2 as 7
2/1/2014	1:08	1:13	-	5	-	-	-	-	-	Correct
2/1/2014	1:23	2:15	-	60	52	-	-	-	-	Hour 2 Should have 37 which is 60-23 and hour 3 as 15

<colgroup><col><col><col><col span="7"><col></colgroup><tbody>
</tbody>

 

[Snakehips]

Hi Bertie & Snakehips,

Must say you guys are geniuses and I really appreciate the quick response.. but still stuck.. with following error (please refer to the last col "Comment" for my issues

EventStartDate	StartTime	EndTime	Hour 1	Hour 2	Hour 3	Hour 4	Hour 5	Hour 6	Hour 7	Comment
2/1/2014	0:00	0:03	3	-	-	-	-	-	-	Correct
2/1/2014	0:00	0:07	7	-	-	-	-	-	-	Correct
2/1/2014	0:37	2:07	60	60	30	-	-	-	-	Hour 1 Should have 23 which is 60-37 and hour 2 as 7
2/1/2014	1:08	1:13	-	5	-	-	-	-	-	Correct
2/1/2014	1:23	2:15	-	60	52	-	-	-	-	Hour 2 Should have 37 which is 60-23 and hour 3 as 15

<TBODY>
</TBODY>

nisbad,

It's obvious now you point it out that my previous train of thought was a little off.


I think that your third comment down should read 'Hour 1 60-37 = 23 Hour 2 = 60 Hour 3 = 7 ??


That being the case, here is a revised solution that hopefully has all angles covered.

Excel 2007
	A	B	C	D	E	F	G	H	I
1	EventStartDate	StartTime	EndTime	Hour 1	Hour 2	Hour 3	Hour 4	Hour 5	Hour 6
2	11/01/2013	00:00	00:03	3	-	-	-	-	-
3	11/01/2013	00:00	00:07	7	-	-	-	-	-
4	11/01/2013	00:37	02:07	23	60	7	-	-	-
5	11/01/2013	01:08	01:13	-	5	-	-	-	-
6	11/01/2013	01:23	02:15	-	37	15	-	-	-
7	11/01/2013	00:00	05:14	60	60	60	60	60	14
8	11/01/2013	01:23	02:15	-	37	15	-	-	-
Sheet2

Cell Formulas
Range	Formula
D2	=IF(AND(HOUR($B2)+1=COLUMNS($B2:B2),MINUTE($B2)>0),IF(HOUR($B2)=HOUR($C2),MINUTE($C2)-MINUTE($B2),60-MINUTE($B2)),IF(HOUR($C2)=COLUMNS($B2:B2)-1,MINUTE($C2),IF(AND(HOUR($C2)>=COLUMNS($B2:B2),HOUR($B2)$B2:B2)),60,"-")))

 

[nisbad]

Awesome Sir.. You are a genius..

Perfect solution.. i was working on this for last 2 months.. and you solved in less than 24 hours..

Really appreciate the help..

thx

Nishant

nisbad,

It's obvious now you point it out that my previous train of thought was a little off.


I think that your third comment down should read 'Hour 1 60-37 = 23 Hour 2 = 60 Hour 3 = 7 ??


That being the case, here is a revised solution that hopefully has all angles covered.


Excel 2007

	A	B	C	D	E	F	G	H	I
1	EventStartDate	StartTime	EndTime	Hour 1	Hour 2	Hour 3	Hour 4	Hour 5	Hour 6
2	11/01/2013	00:00	00:03	3	-	-	-	-	-
3	11/01/2013	00:00	00:07	7	-	-	-	-	-
4	11/01/2013	00:37	02:07	23	60	7	-	-	-
5	11/01/2013	01:08	01:13	-	5	-	-	-	-
6	11/01/2013	01:23	02:15	-	37	15	-	-	-
7	11/01/2013	00:00	05:14	60	60	60	60	60	14
8	11/01/2013	01:23	02:15	-	37	15	-	-	-

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D2 =IF(AND(HOUR($B2)+1=COLUMNS($B2:B2),MINUTE($B2)>0),IF(HOUR($B2)=HOUR($C2),MINUTE($C2)-MINUTE($B2),60-MINUTE($B2)),IF(HOUR($C2)=COLUMNS($B2:B2)-1,MINUTE($C2),IF(AND(HOUR($C2)>=COLUMNS($B2:B2),HOUR($B2)<COLUMNS($B2:B2)),60,"-"))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>