# Calculating time interval

#### RobD

##### New Member
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-

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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

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
Thanks -Rob-

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
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

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.

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

If you want to discount the weekends, use NetWorkDays.

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.

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-

[/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).

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-

Replies
3
Views
137
Replies
12
Views
460
Replies
7
Views
620
Replies
2
Views
702
Replies
8
Views
902

1,218,575
Messages
6,143,311
Members
450,477
Latest member
teresab543

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back