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-

 

[plettieri]

Hello RobD

There are numberous inquiries about time/date calculations on this forum...It might give us a better idea if you could show a short sample of your data and expected outcome...and maybe we can improve on that for you...

pll

 

[RobD]

OK, pretty straight forward..
Start: 11/02/2002 12:00:00 PM
exclude the times between 4:00:00AM and 10:00:00 AM
End: 11/05/2002 6:30:00 AM
expected answer: 52:00:00
Thanks -Rob-

 

[Dave Patton]

On 2002-10-08 12:39, RobD wrote:
OK, pretty straight forward..
Start: 11/02/2002 12:00:00 PM
exclude the times between 4:00:00AM and 10:00:00 AM
End: 11/05/2002 6:30:00 AM
expected answer: 52:00:00
Thanks -Rob-

You did not indicate if the date-time is in one or two cells; revise as appropriate.

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

With dates in B2 and B3 18 hours is .75 day
For convenience I put 24: in F2 and 4: in F3

Custom format the cell [h]:mm

 

[IML]

Not fully tested, but here is what I came up

=IF(INT(A1)=INT(A2),MAX(0,MOD(A2,1)-MAX(MOD(A1,1),$J$2))+MAX($J$1-MOD(A1,1),0),MAX(INT(A2)-INT(A1)-1,0)*(1-($J$2-$J$1))+MIN(1-MOD(A1,1),1-$J$2)+MAX(0,$J$1-MOD(A1,1))+MIN(MOD(A2,1),$J$1)+MAX(MOD(A2,1)-$J$2,0))

where A1 is start time, A2 is stop time.
J1 houses your lower limit (4:00)and J2 your upper limit (10:00). All cells need to be true time/dates.

I haven't played with Dave's yet, but if it works go with the simplier one.

 

[stevebausch]

I thought elapsed time was simple math: subtract date-time from another date-time.

If you want to discount the weekends, use NetWorkDays.

 

[IML]

On 2002-10-08 13:48, stevebausch wrote:
I thought elapsed time was simple math: subtract date-time from another date-time.

If you want to discount the weekends, use NetWorkDays.

This, however, doesn't take into account excluding the hours in question.

 

[RobD]

On 2002-10-08 13:34, IML wrote:
Not fully tested, but here is what I came up

=IF(INT(A1)=INT(A2),MAX(0,MOD(A2,1)-MAX(MOD(A1,1),$J$2))+MAX($J$1-MOD(A1,1),0),MAX(INT(A2)-INT(A1)-1,0)*(1-($J$2-$J$1))+MIN(1-MOD(A1,1),1-$J$2)+MAX(0,$J$1-MOD(A1,1))+MIN(MOD(A2,1),$J$1)+MAX(MOD(A2,1)-$J$2,0))

where A1 is start time, A2 is stop time.
J1 houses your lower limit (4:00)and J2 your upper limit (10:00). All cells need to be true time/dates.

This comes pretty close to what I am trying to do, but still doesn't seem to allow for more than 24 hours in the result. The example I provided works out to 4:00:00 instead of 52:00:00.
The date and time are in a single cell, but can be made into seperate cells for date and time if it would be easier.
-Rob-

 

[IML]

[/quote]

The example I provided works out to 4:00:00 instead of 52:00:00.
[/quote]

Which ever is easier for you. This sounds like a formatting issue, the formula check out on your date. Change the format of the cell to [h]:mm as dave suggested. You are seeing only the incremental four hours and not the 4 days (4*24 = 48 + 4 = 52).

 

[RobD]

On 2002-10-08 16:44, IML wrote:
Change the format of the cell to [h]:mm as dave suggested.

OK, that worked. I don't quite understand why the [h] made the difference, but I think I can figure that one out on my own
Thanks very much for your help guys.
-Rob-