Time breakdown function in Excel

[tonycm1]

I have an excel sheet that contains the following information:

Name Start End Duration
Joe Smith 3:26:55 23:49:06 20:22:11

If I wanted to break down (per Name) how many hours between 7am-5pm people spend in a 4th column, any suggestions how I can do this?

[unmarkedhelicopter]

Er ... in a 5th column you mean ?
To clarify, for the example above :-
03:26:55 start doesn't count till 07:00 ?
and 23:49:06 doesn't count after 17:00 ?

How about : =IF(C2>17/24,17/24,C2)-IF(7/24>B2,7/24,B2)

[royUK]

Cross posted here

http://www.excelforum.com/showthread.php?p=1858121#post1858121

[unmarkedhelicopter]

cross posting ... naughty tonycm1 !!!

This wastes peoples time and is not appreciated, your likey resoponses in future will drop dramatically because of this.

You should also note that, with Excel, there is 'sometimes' a rounding error with 'times' that means that with 'some calculations' you may get an error. But it would be a small one.

[tonycm1]

my apologies unmarkedhelicopter.... i didn't read the rules, but now I know better for next time :)

I have experience in C++ but am trying to learn more and more about VBA since it's so useful in Excel so i'll be frequenting this forum a lot in the future :) I've already learned a lot by reading a few posts on the site :)

[tonycm1]

For anyone who is interested in the original question and for the forum's reference, the solution was adding a 5th column that has the following formula:

=(B2>C2)*MEDIAN(0,C2-7/24,5/12)+MAX(0,MIN(17/24,C2+(B2>C2))-MAX(7/24,B2))

[unmarkedhelicopter]

=(B2>C2)*MEDIAN(0,C2-7/24,5/12)+MAX(0,MIN(17/24,C2+(B2>C2))-MAX(7/24,B2))What was wrong with :-
=IF(C2>17/24,17/24,C2)-IF(7/24>B2,7/24,B2) ???

[tonycm1]

Hey unmarkedhelicopter,

=IF(C2>17/24,17/24,C2)-IF(7/24>B2,7/24,B2) works great if your assuming that nobody every works an overnight shift (ie. 10:00pm to 9:00am), in which case, it returns a negative time value instead of 2:00. Also, if you put that someone started at 22:00 and ended at 6:30, it SHOULD return a value of 0 (since no time was between 7am-5pm), but it returns a value of -15:30.

Your formula however, would work in VBA if i were to use a bunch of nested if statements within a do, loop (until row = "").

[unmarkedhelicopter]

You did not mention that a shift could go across midnight, no data you included said so.