Time differences in excel

[Nebuchanezer]

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?

 

[Bob Phillips]

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)>=TIME(23,0,0),MOD(A22,1)-TIME
(23,0,0),0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Nebuchanezer" <[email protected]>
wrote in message
news:[email protected]...

 

[Bernie Deitrick]

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