Calculating difference between dates and times

[Kaurr]

Hi guys, I am trying to calculate downtime between two dates.

Taking the first as example: Outage started on 1st and ended on 19th. Now Between 1st and 19th, I only want to calculate outage from 10:00 to 20:00 (support hours).

Taking the second row as example - clearly on sunday (5th Oct), there is the outage of 9 mins however it calculates 10 hours (total support hours)..??

Started	Ended	Total Duration	Service Start Time (Mon-Sun)	Service end time (Mon-Sun)	Service Hrs (Sun)	Service Support Type	Outage hrs	Outage hrs (Sunday)
01-10-2014 10:14	19-10-2014 21:28	26593	10:00	20:00	10:00	10x7 Support	159:45:01	30:00:00
05-10-2014 10:13	05-10-2014 10:23	9	10:00	20:00	10:00	10x7 Support	0:00:00	10:00:00
05-10-2014 17:56	05-10-2014 23:43	346	10:00	20:00	10:00	10x7 Support	0:00:00	10:00:00
05-10-2014 18:00	05-10-2014 18:20	19	10:00	20:00	10:00	10x7 Support	0:00:00	10:00:00
05-10-2014 18:17	05-10-2014 18:35	18	10:00	20:00	10:00	10x7 Support	0:00:00	10:00:00
05-10-2014 18:37	05-10-2014 19:00	22	10:00	20:00	10:00	10x7 Support	0:00:00	10:00:00
05-10-2014 20:04	05-10-2014 20:46	41	10:00	20:00	10:00	10x7 Support	0:00:00	10:00:00
05-10-2014 20:39	05-10-2014 21:04	25	10:00	20:00	10:00	10x7 Support	0:00:00	10:00:00

<TBODY>
</TBODY>

Using the following formula i am able to calculate the outage from Monday to saturday (Outage hrs)
=IF(C2="",0,(NETWORKDAYS.INTL(B2,C2,11)-1)*(F2-E2)+IF(NETWORKDAYS.INTL(C2,C2,11),MEDIAN(MOD(C2,1),E2,F2),F2)-MEDIAN(NETWORKDAYS.INTL(B2,B2,11)*MOD(B2,1),E2,F2))

but unable to calculate the downtime of sundays (outage hours (sunday)), for which I am using the following formula:

=IFERROR(G2*SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(B2)&":"&INT(C2))))=1)),0)

B2 - Start date
C2 - End Date
D2 - Total Duration
E2 - Service start time
F2 - Service end time
G2 - serice hours sunday
H2 - Service support type
J2 - outage hours
K2 - Outage hours (sun)


Thanks for your help in advance!!

 

[skywriter]

Try this:

Excel 2010
	B	C	D	E	F	G	H	I	J
1	Started	Ended	Total Duration	Service Start Time (Mon-Sun)	Service end time (Mon-Sun)	Service Hrs (Sun)	Service Support Type	Outage hrs	Outage hrs (Sunday)
2	Oct. 1, 2014 10:14	Oct. 19, 2014 21:28	26594	10:00	20:00	10:00	10x7 Support	159:46:00	30:00:00
3	Oct. 5, 2014 10:13	Oct. 5, 2014 10:23	10	10:00	20:00	10:00	10x7 Support	0:00:00	0:10:00
4	Oct. 5, 2014 17:56	Oct. 5, 2014 23:43	347	10:00	20:00	10:00	10x7 Support	0:00:00	2:04:00
5	Oct. 5, 2014 18:00	Oct. 5, 2014 18:20	20	10:00	20:00	10:00	10x7 Support	0:00:00	0:20:00
6	Oct. 5, 2014 18:17	Oct. 5, 2014 18:35	18	10:00	20:00	10:00	10x7 Support	0:00:00	0:18:00
7	Oct. 5, 2014 18:37	Oct. 5, 2014 19:00	23	10:00	20:00	10:00	10x7 Support	0:00:00	0:23:00
8	Oct. 5, 2014 20:04	Oct. 5, 2014 20:46	42	10:00	20:00	10:00	10x7 Support	0:00:00	0:00:00
9	Oct. 5, 2014 20:39	Oct. 5, 2014 21:04	25	10:00	20:00	10:00	10x7 Support	0:00:00	0:00:00
Sheet3

Cell Formulas
Range	Formula
D2	=(C2-B2)*1440
I2	=IF(C2="",0,(NETWORKDAYS.INTL(B2,C2,11)-1)*(F2-E2)+IF(NETWORKDAYS.INTL(C2,C2,11),MEDIAN(MOD(C2,1),E2,F2),F2)-MEDIAN(NETWORKDAYS.INTL(B2,B2,11)*MOD(B2,1),E2,F2))
J2	=IF(C2="",0,(NETWORKDAYS.INTL(B2,C2,"1111110")-1)*(F2-E2)+IF(NETWORKDAYS.INTL(C2,C2,"1111110"),MEDIAN(MOD(C2,1),E2,F2),F2)-MEDIAN(NETWORKDAYS.INTL(B2,B2,"1111110")*MOD(B2,1),E2,F2))

 

[Kaurr]

It works!!! Perfect, thank you

 

[skywriter]

My pleasure.