Need help calculating hours and minutes in a rolling 24 hour period with "If/Then" also

I'm creating a time sheet to calculate when a person has accumulated 16 hours within a 24 hour period. e.g.
1st work assignment: 7 AM til 3:30 PM with a half hour break for lunch. = 8.5 hours
2nd work assignment: 5:21 PM til 8:42 PM = 3 hours. so the total now is 11.5 BUT, because the interval between 3:30 and 5:21 is less than 2 hours (120 minutes), the time continues to accumulate, therefore, I need the total to be 13 hours.
If the 2nd work assignment had started at 5:31 PM, then the interval time would not count, thus the total would be 11.5.
Time Sheet.xlsx
The next problem is, if the second work assignment is less than 2 hours, I need to accumulate 2.5 hours.
I also don't know how to calculate the hours if the second work assignment starts at 23:42 on day one and stops at 3:08 on day 2.
Thanks for your help.

First of all, you don't write

Formula:

Please Login or Register  to view this content.

because there is a SUM function where you write

Formula:

Please Login or Register  to view this content.

Same for other.

Second, I don't understand. You did get 13 hours as result?

Thanks for the reply. When I changed the formula as you stated, I get 2:46 as a total. And yes, I did get 13, but, when I change B10 to 17:35, the formula doesn't work unless I edit reverse the "<" sign . . .
Also, how do I calculate the time when I start the next work assignment at 23:32 and end it the next day at 3:08 in the morning?

Can you give me an example? Start at 23:42 on day one and end at 3:08 on day 2 . . . on my fingers its 3.5 hours, but I want Excel to tell me that.

Assume 23:42 is in A1 and 3:08 in B1 - put this formula in C1:

=B1-A1+(A1>B1)

Format the cell as time, and you should see 3:26 - not sure why you think it should be 3.5, but if you want to see the result in hours, then you can do this:

=(B1-A1+(A1>B1))*24

and format the cell as General or Number - you should see 3.43333.

Hope this helps.

Pete

Thanks - I really appreciate the help.
As to why I think its 3.5 hours from 23:42 to 3:08 - it's because (in our world) the computed time starts at 23:30 (at 23:45 the time would have started at 12 AM) and the end time of 3:08 backs up to 3:00., so the time was calculated as 11:30 PM to 3 AM - or 3.5 hours.
(e.g. from 01:45 till 02:14, the time starts at 02:00, from 02:15 till 02:44, time starts at 02:30 and so on - )
Which brings up my next question:
How can I get Excel to see it that way - and display it that way?

Like this if you want it in hours:

=(FLOOR(B1,1/24/4)-FLOOR(A1,1/24/4)+(A1>B1))*24

This rounds each time down to quarter of an hour (that's why I have left it as 1/24/4 - easier to understand).

Hope this helps.

Pete

That is unbelievably AWESOME! Dude, you must think in code . . . I'm totally dazzled . . .

In A1 I have "15:30" in B1 I have "17:14". In Cell C1 I want so see "0" if B1-A1 is greater than 2 hours. If it's less than 2 hours, I want the actual time, rounded up or down as described above.

You can do that with this:

=MIN((FLOOR(B1,1/24/4)-FLOOR(A1,1/24/4)+(A1>B1))*24,2)

Changes in red.

Hope this helps.

Pete

Sorry, you want zero if the difference is greater than 2 hours ??

Strange ...

but you can do that with this formula:

=IF((FLOOR(B1,1/24/4)-FLOOR(A1,1/24/4)+(A1>B1))*24>2,0,(FLOOR(B1,1/24/4)-FLOOR(A1,1/24/4)+(A1>B1))*24)

Hope this helps.

Pete

Well, When I paste that in, I get "1.5" in C1. That's fine. But if I change B1 to "17:35", it shows me "2", even if I change it to "23:50" it shows me "2". I need it to show me "0" if the difference is greater than 2 hours.

Thank you so much for your help (and patience).

BTW - I was in Carnforth, England back in '86. I became addicted to fish and chips! Can't find ANYTHING in the US that even comes close . . . sigh

See my later post. Originally I thought you wanted the difference not to exceed 2 hours (e.g. the maximum allowed overtime), but then I re-read your post.

Hope this helps.

Pete