Time Calculation

[CatherineMadden]

How do I get the hours worked to not be negative numbers and to be correct? They are employees that work a 10 (3p-1a) or 12 hour (3p-3a) shift. Their shift starts "today" and ends "tomorrow".

[#2]

@CatherineMadden Like so:

Works also for times from 3 PM to 11 PM.

[#3]

@Riny_van_Eekelen 

When I used that formula, this is what I got:

[#4]

Never mind, I needed to change the time format, it worked perfectly. Thank you.

[#5]

@Riny_van_Eekelen 

Is there a way that I can get the OT Hrs to be a whole number? I was hoping that I could take the hours worked and subtract the normal shift to calculate over time hours but it gives the error message.

[#6]

@CatherineMadden Presumably I4 contains 8 based on a time format. Thus the underlying number value is 8/24. That's how Excel stores times. If the 8 in A4 is the plain number 8, the formula will return a negative value that you can not display as time. Change the formula to

=I4-A4/24

That should work.

[#7]

@CatherineMadden 

Riny's formula is valid to get an elapsed time value (duration) into AO4, but it could produce negative numbers.  Consider this instead:

=MAX( (I4-A4/24), 0 )

(In all these examples, the spaces are not required, but are included for readability.)

But if you need an actual whole number there, you need to decide how that should be calculated, as employees may work partial hours as well (I presume). E.g., what if Jane actually worked some minutes after 11:00 PM? Try one of these formulas:

To ignore fractions of an hour:
=ROUNDDOWN( MAX( (I4-A4/24) * 24, 0 ), 0 )
To round up at half an hour:
=ROUND( MAX( (I4-A4/24) * 24, 0 ), 0 )
If working any portion of an hour gets you wages for the full hour:
=ROUNDUP( MAX( (I4-A4/24) * 24, 0 ), 0 )

Edit: I was in a hurry, and messed up the alternative to Riny's formula.  It's fixed now.