Time differences in excel
 

I need to work out the difference in minutes between 2 times usually
spread over 2 to 3 days, but I also need to exclude any of those
minutes that come between 06:00 and 23:00 each day over that period.

For Example:

Start Date 3/1/2006 07:36
End Date 6/1/2006 19:00
Times to exclude 3/1/2006 06:00-23:00, 4/1/2006 06:00-23:00, 5/1/2006
06:00-23:00, 6/1/2006 06:00-23:00.

Can anyone help?


--
Nebuchanezer
------------------------------------------------------------------------
Nebuchanezer's Profile: http://www.excelforum.com/member.php...o&userid=30186
View this thread: http://www.excelforum.com/showthread...hreadid=498663

Time differences in excel
 

Assuming the dates are in A21 and A22, try this

=(INT(A22)-INT(A21)-2)*7/24+(IF(MOD(A21,1)<=TIME(6,0,0),TIME(6,0,0)-MOD(A21,
1),0)+IF(MOD(A21,1)<23,MIN(TIME(1,0,0),1-MOD(A21,1)),0))+(IF(MOD(A22,1)<=TIM
E(6,0,0),MOD(A22,1),TIME(6,0,0))+IF(MOD(A22,1)=TI ME(23,0,0),MOD(A22,1)-TIME
(23,0,0),0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Nebuchanezer"
wrote in message
news:Nebuchanezer.217sao_1136553009.5434@excelforu m-nospam.com...

I need to work out the difference in minutes between 2 times usually
spread over 2 to 3 days, but I also need to exclude any of those
minutes that come between 06:00 and 23:00 each day over that period.

For Example:

Start Date 3/1/2006 07:36
End Date 6/1/2006 19:00
Times to exclude 3/1/2006 06:00-23:00, 4/1/2006 06:00-23:00, 5/1/2006
06:00-23:00, 6/1/2006 06:00-23:00.

Can anyone help?


--
Nebuchanezer
------------------------------------------------------------------------
Nebuchanezer's Profile:
http://www.excelforum.com/member.php...o&userid=30186
View this thread: http://www.excelforum.com/showthread...hreadid=498663

Time differences in excel
 

With the earlier date time in cell A1, and the later date time in A2, array enter, using
Ctrl-Shift-Enter, this formula:

=SUM(IF((HOUR(A1+ROW(INDIRECT("A1:A"&TEXT((A2-A1)*1440,"0")))/1440)=23)+(HOUR(A1+ROW(INDIRECT("A1:A"&TEXT((A2-A1)*1440,"0")))/1440)<=6),1,0))

HTH,
Bernie
MS Excel MVP


"Nebuchanezer" wrote in message
news:Nebuchanezer.217sao_1136553009.5434@excelforu m-nospam.com...

I need to work out the difference in minutes between 2 times usually
spread over 2 to 3 days, but I also need to exclude any of those
minutes that come between 06:00 and 23:00 each day over that period.

For Example:

Start Date 3/1/2006 07:36
End Date 6/1/2006 19:00
Times to exclude 3/1/2006 06:00-23:00, 4/1/2006 06:00-23:00, 5/1/2006
06:00-23:00, 6/1/2006 06:00-23:00.

Can anyone help?


--
Nebuchanezer
------------------------------------------------------------------------
Nebuchanezer's Profile: http://www.excelforum.com/member.php...o&userid=30186
View this thread: http://www.excelforum.com/showthread...hreadid=498663