Time Formula

[kumara_faith]

Hi,

I have the following sheet that calculates the downtime in branches based on the specific operating hours everyday.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	*			*	Monday	Monday	Tuesday	Tuesday	Wednesday	Wednesday	Thursday	Thursday	Friday	Friday	Saturday	Saturday	Sunday	Sunday	*
3	Start Date	Start Time	End Date	End Time	Start Time	End Time	Start Time	End Time	Start Time	End Time	Start Time	End Time	Start Time	End Time	Start Time	End Time	Start Time	End Time	Downtime
4	2/14/2011	8.58 PM	2/16/2011	9.06 AM	9.30 AM	10.30 PM	9.30 AM	10.30 PM	9.30 AM	10.30 PM	9.30 AM	10.30 PM	9.30 AM	11.00 PM	9.30 AM	11.00 PM	9.30 AM	10.30 PM	36.08.00
Sheet1

The downtime ahould only take into account during the operating hours and should exclude hours of downtime during non operating hours.

In the example above, the actual downtime during operating hours is 14 hours and 32 minutes. Although the downtime lasted from Feb 14 to Feb 16, the downtime hours that affected operating hours was 14 hours 32 minutes and not 36 hours and 8 minutes.

Is there anyway we could build a formula for this ?

Appreciate all the help.

 

[kumara_faith]

bump.

 

[kumara_faith]

Hi,

I have a cross post here:

http://www.ozgrid.com/forum/showthread.php?t=151155

 

[kumara_faith]

bump

 

[barry houdini]

Try this formula in T4

=SUMPRODUCT(INT((WEEKDAY(B4-{2,3,4,5,6,7,1})+D4-B4)/7),CHOOSE({1,2,3,4,5,6,7},G4-F4,I4-H4,K4-J4,M4-L4,O4-N4,Q4-P4,S4-R4))-MAX(0,MIN(INDEX(F4:S4,WEEKDAY(B4,2)*2),C4)-INDEX(F4:S4,WEEKDAY(B4,2)*2-1))-MAX(0,INDEX(F4:S4,WEEKDAY(D4,2)*2)-MAX(INDEX(F4:S4,WEEKDAY(D4,2)*2-1),E4))