Error in subtracting time - Help

[mndpy]

I have a formula that is subtracting time and when the person works more than
4.99 hours it is automatically taking .5 out for the lunch break. Well the
formula is working for the most part however it appears not to work when the
start time is on the hour it bring back an incorrect value.

Formula:

=MOD(K20-K19,1)-IF(MOD(K20-K19,1)>4.99/24,0.5/24,0)

The incorrect value:

9:00
14:50

Answer: 5:20

I anyone can help let me know. Thanks!

 

[Ron Coderre]

In your posted example

StartTime: 9:00 AM
EndTime: 2:50 PM

Total Time: 5 hours and 50 minutes
Less: 30 minutes
Equals: 5 hours 20 minutes

That's what your formula returns.

Where are you seeing an error?
***********
Regards,
Ron

XL2003, WinXP

 

[Mike H]

I see no error the times you give do equate to 5:20

Please clarify.

Mike

 

[David Biddulph]

Why do you say it is incorrect? It seems to be doing what you've asked it
to do. The time difference is 5:50, which is greater than 4.99 hours, so it
subtracts an extra 30 minutes, giving 5:20.

What did you expect it to do?

 

[Teethless mama]

Your formula is working fine.
Try the formula below. It's much shorter than the one you have.

=K20-K19-(K20-K19>4.99/24)*0.5/24

 

[Ron Coderre]

Don't forget to wrap your formulas in the MOD functions:

=MOD(K20-K19-(MOD(K20-K19,1)>4.99/24)*0.5/24,1)

Without MOD, midnight crossovers become an issue. If the work starts at 11
PM and ends at 5 AM the next day (6 hours), your formula returns a negative
time value (-0.75) instead of 5.5 hours.
***********
Regards,
Ron

XL2003, WinXP