Difference in dates calculations except between certain times.

[Guest]

I am trying to calculate the difference between two date/time entries in
minutes excluding a certain period of time.

EG date 1:- 1/10/2006 19:30; date 2:- 1/11/2006 10:00. i am aware that the
formula for calculating out the minutes is =(2nd date-1st date)*1440 which
returns the answer 870 but i am trying to exclude the time between 23:00 and
07:00 the following morning so i would like the answer to return 390 minutes.
The formula would have to work between multiple days such as 1/10/2006 -
4/01/2006 i.e a spilt of 3 days but excluding the times 23:00 to 07:00 as
above.

Please advise.

 

[Bob Phillips]

Try this

=(INT(A2)-INT(A1)-1)*960+(MIN(TIME(16,0,0),MAX(0,TIME(23,0,0)-MOD(A1,1)))+MI
N(TIME(16,0,0),MAX(0,MOD(A2,1)-TIME(7,0,0))))*1440

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)