# Hours between 2 intervals

1. ## Hours between 2 intervals

I need a way to calculate hours between two intervals. Example 3:00pm and 1:00am should yield 10. I'm using this to determine shift length for a day, then adding up multiple days to get hours scheduled in a week.

Currently I'm using "=b1-a1+(b1<a1)*24" where a1 is 3:00pm and b1 is 1:00am. This formula does yield the accurate result; however, when I attempt to add up multiple formulas like this to get a weekly total, my weekly total becomes astronomically high, and I have no idea why. I also always run into an issue where this becomes more difficult to calculate if the shift traverses midnight.  Register To Reply

2. ## Re: Hours between 2 intervals

If A1 and B1 contain Excel times of the form 12:34, the formula should be:

"=( b1-a1+(b1<=a1) )*24

The important thing is the addition of the outer parentheses.

Changing "<" to "<=" is an improvement. It ensures that when A1 and B1 appear to be the same time, their difference is interpreted as 24 hours, not zero.

We cannot say with impunity that will fix all of your problems because you neglected to provide specifics, namely a set up of pairs of numbers and the result that you get when you add them that is "astronomical".  Register To Reply

3. ## Re: Hours between 2 intervals

Hey joeu2004,

My apologies for not clarifying. I've attached a file with an example of the issue I've ran into. Cells S2:Y2 are calculating the hours worked correctly; however, cell Z2, is yielding 116 hours, when the schedule shows the agent working 7.5 hours 5 days in the week.

Hopefully this helps clarify. Let me know if you need something else.  Register To Reply

4. ## Re: Hours between 2 intervals

First, since you multiply the Excel time arithmetic by 24, you are calculating decimal hours.

So the format of S2:Y2 should be Number, just like Z2 and AA2.

Second, you should apply the correction that I suggested previously.

The formulas should be of the form:

=IFERROR( ( F2-E2+(F2 <= E2) )*24,"")

Then everything will be copacetic.  Register To Reply

5. ## Re: Hours between 2 intervals

Thank you! This worked great!  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 