If formula for selecting an Hour duration

Hi all,

I have two start times, one at

05:00 and the other at 07:00

The work day finishes at 17:00

In B2 if I select 05:00, I want the formula in B3 to =MAX(B6−"12h","0h")

But if I select B2 to start time at 07:00 I want the formula in B3 to ==MAX(B6−"10h","0h")

Cell C1 is adding 30 mins to Shutdown time for getting back to base,

I am trying to say if someone starts at 05:00 and ends at 17:00 overtime is after 17:00

If someone starts at 07:00 and finishes at 17:00 their overtime is cal after the 17:00

Is there an easier way for this?

Mac mini, macOS 13.5

Posted on Aug 20, 2023 12:35 PM

Best answer

Level 2

311 points

So basically anything beyond 17:00 is overtime, whatever time they started, right? Can the landed time in B3 be entered manually?

If so, then in B7 use this formula.

=MAX(B4−"17:00",DURATION(,,0))

I assume in B4 you have

=B3+C2

Posted on Aug 21, 2023 7:22 AM

View in context

Similar questions