Calculating time interval

[RobD]

I know this has been addressed before and have searched the archive but so far I've been unable to find something that works.

I need a formula to calculate the total number of hours and minutes between two date/time values. In addition the calculation must ignore the hours between 4:00AM and 10:00AM, include all weekends and holidays and have the ability to span multiple days.
I have tried simple subtraction but Excel doesn't seem to allow for more than a 24 hour difference. I also tried a solution I saw on http://www.mvps.org/dmcritchie/excel/datetime.htm but can't seem to get it to work either.
Any help appreciated.
Thanks -Rob-

 

[RobD]

Arggg! Still have a quirk with this. For some reason the following values produce 0:52:35 instead of the expected 0:01:00.
Any ideas?

11:00 AM 2:00 AM


23-Apr-02 1:07:25 AM
23-Apr-02 1:08:25 AM

0:52:35

=IF(INT(A3)=INT(A4),MAX(0,MOD(A4,1)-MAX(MOD(A3,1),$A$1))+MAX($B$1-MOD(A3,1),0),MAX(INT(A4)-INT(A3)-1,0)*(1-($A$1-$B$1))+MIN(1-MOD(A3,1),1-$A$1)+MAX(0,$B$1-MOD(A3,1))+MIN(MOD(A4,1),$B$1)+MAX(MOD(A4,1)-$A$1,0))

 

[IML]

Oops. How about
=IF(INT(A3)=INT(A4),IF(OR(MOD(A4,1)<B1,MOD(A3,1)>A1),A4-A3,MAX(0,MOD(A4,1)-MAX(MOD(A3,1),$A$1))+MAX($B$1-MOD(A3,1),0)),MAX(INT(A4)-INT(A3)-1,0)*(1-($A$1-$B$1))+MIN(1-MOD(A3,1),1-$A$1)+MAX(0,$B$1-MOD(A3,1))+MIN(MOD(A4,1),$B$1)+MAX(MOD(A4,1)-$A$1,0))

I didn't account for both times being before or after the times when on the same day.

edited to disable HTML
This message was edited by IML on 2002-10-09 14:24

 

[Dave Patton]

With Day and Time in separate cells

=MAX(0,(B3-B2-1))*0.75+(C2>F2)*F2-C2+MIN(C3,F3)+MAX(C3-F4,0)

With Day and Time in the Same cell

=MAX(0,(DATEDIF(A1,A2,"d")-1))*0.75+(HOUR(A1)>10)*F2-TIME(HOUR(A1),MINUTE(A1),0)+MIN(TIME(HOUR(A2),MINUTE(A2),0),F3)+MAX(0,TIME(HOUR(A2),MINUTE(A2),0)-F4)