software

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: www.excelforum.com/member.php...oamp;userid=30186
View this thread: www.excelforum.com/showthread...hreadid=498663Assuming the dates are in A21 and A22, try this

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Nebuchanezerquot; gt;
wrote in message
news:Nebuchanezer.217sao_1136553009.5434@excelforu m-nospam.com...
gt;
gt; I need to work out the difference in minutes between 2 times usually
gt; spread over 2 to 3 days, but I also need to exclude any of those
gt; minutes that come between 06:00 and 23:00 each day over that period.
gt;
gt; For Example:
gt;
gt; Start Date 3/1/2006 07:36
gt; End Date 6/1/2006 19:00
gt; Times to exclude 3/1/2006 06:00-23:00, 4/1/2006 06:00-23:00, 5/1/2006
gt; 06:00-23:00, 6/1/2006 06:00-23:00.
gt;
gt; Can anyone help?
gt;
gt;
gt; --
gt; Nebuchanezer
gt; ------------------------------------------------------------------------
gt; Nebuchanezer's Profile:
www.excelforum.com/member.php...oamp;userid=30186
gt; View this thread: www.excelforum.com/showthread...hreadid=498663
gt;
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(quot;A1:Aquot;amp;TEXT((A2-A1)*1440,quot;0quot;)))/1440)gt;=23) (HOUR(A1 ROW(INDIRECT(quot;A1:Aquot;amp;TEXT((A2-A1)*1440,quot;0quot;)))/1440)lt;=6),1,0))

HTH,
Bernie
MS Excel MVPquot;Nebuchanezerquot; gt; wrote in message
news:Nebuchanezer.217sao_1136553009.5434@excelforu m-nospam.com...
gt;
gt; I need to work out the difference in minutes between 2 times usually
gt; spread over 2 to 3 days, but I also need to exclude any of those
gt; minutes that come between 06:00 and 23:00 each day over that period.
gt;
gt; For Example:
gt;
gt; Start Date 3/1/2006 07:36
gt; End Date 6/1/2006 19:00
gt; Times to exclude 3/1/2006 06:00-23:00, 4/1/2006 06:00-23:00, 5/1/2006
gt; 06:00-23:00, 6/1/2006 06:00-23:00.
gt;
gt; Can anyone help?
gt;
gt;
gt; --
gt; Nebuchanezer
gt; ------------------------------------------------------------------------
gt; Nebuchanezer's Profile: www.excelforum.com/member.php...oamp;userid=30186
gt; View this thread: www.excelforum.com/showthread...hreadid=498663
gt;