Cell Formatting Negative Zero

[RTH10260]

Hello!
I am just adapting simple work hours collection sheet.
The row has 3 pairs of start/stop times, one cell for break, one cell for correction, all defined as Date/Time.
I make a calculation into workhours [K19] =(D19-C19)+(F19-E19)+(H19-G19)-I19+J19
Then calculate potential overtime as [L19] =IF(K19=0;"";K19-($G$8*$G$9))

Problem:
using the cell custom format of {[h]:mm;[Red]"-"[h]:mm;""} on the overtime [L19] I get a red negative zero rather than an empty field displayed.

Any suggestions as how to get arround the obvious rounding issue in the Date/Time calculation?

[SuperTech]

Thank you for posting your problem. Please send this file so that we can check it at our end.

If your file is private, you can send it to forum[at]softmaker.com with the link of this post.

[Woody44]

The row has 3 pairs of start/stop times, one cell for break, one cell for correction, all defined as Date/Time.
I make a calculation into workhours [K19] =(D19-C19)+(F19-E19)+(H19-G19)-I19+J19
Then calculate potential overtime as [L19] =IF(K19=0;"";K19-($G$8*$G$9))

Problem:
using the cell custom format of {[h]:mm;[Red]"-"[h]:mm;""} on the overtime [L19] I get a red negative zero rather than an empty field displayed.

Any suggestions as how to get arround the obvious rounding issue in the Date/Time calculation?

I think the problem is in your second formula. If K19 is the work hours for the period (call it a week), let's say the standard week before overtime is 40 hours. Anything over 40 hours is overtime.

The first argument in your second formula says that if K19 equals zero, the cell is blank. BUT ... if the employee did any work at all in the week, K19 will never be zero, so L19 can't ever be blank.

What you need is a formula that says if K19 is 40 or less hours, L19 is blank.

=IF(K19<=40,"",K19-($G$8*$G$9))

[RTH10260]

@Woody44: formula is correct, overtime is based on one of the cells in the fomula, not on fixed 40 hours.

[RTH10260]

Received reply in email conversation.

from Support:
Just use ROUND() on the IfElse parameter, for example "=IF(K26=0,"",ROUND(K26-($G$8*$G$9),5))". That should do the trick.

[Woody44]

@Woody44: formula is correct, overtime is based on one of the cells in the fomula, not on fixed 40 hours.

It doesn't matter if the overtime is applied by the day or by the week. I think the change I provided would work -- if by the day, just substitute 8 ( or 7, or 6) in place of the 40. I don't understand how rounding makes any difference, or why you would round labor hours.

But ... if the suggestion from SoftMaker support does what you need, then all is well.

[RTH10260]

@Woody44: formula is correct, overtime is based on one of the cells in the fomula, not on fixed 40 hours.

It doesn't matter if the overtime is applied by the day or by the week. I think the change I provided would work -- if by the day, just substitute 8 ( or 7, or 6) in place of the 40. I don't understand how rounding makes any difference, or why you would round labor hours.

But ... if the suggestion from SoftMaker support does what you need, then all is well.

@woody: the issue is not my formula (it's corect for my application) but the very minor rounding error that happens along the way, due to the way how SoftMaker stores the date/time format in PlanMaker. it's like a 0.00000012345 rather than a plain zero for a zero time value. The workaround removes that pesky ...12345 stuff.