Strange behavior in Excel when counting Work Hours and Work Super Hours

Question

I composed the next Worksheet in order to count my Workhours.

Other solutions proposed over the internet would not work, since the Workhours amount per day was not fixed at first. The problem makes its appearance sometimes when the "Total Work Hours" equal the "Normal Work Hours". When I try to format the Super Hours Cells using conditions, I don't get the correct result. The equality seems not to be the case.

Cell K9: Negative or very big date and time values are displayed as pound signs (###)

The following information are important:

1. The Cells A4:B500, F4, F5 are formatted like: Hour 13:30
2. The Cells J7:R9 are formatted like: Custom [h]:mm
3. J7: =SUM($C4:$C34) and respectively Cells K7,L7...
4. J8: =J4*$F$5 and respectively Cells K8,L8...
5. J9: =IF(J$7>=J$8;J$7-J$8;J$8-J$7) and respectively Cells K9,L9...

I would like to know why this happens and if there is a solution or a different approach.

Answer 1

When you perform math operations in Excel you will sometimes get floating point errors. They come about because Excel is limited to 15 digit precision so significant digits after 15 are ignored.

Times in Excel are all percentages of days. Computers calculate in binary so all those decimals have to be converted to binary before the math operation and the result has to be converted back to decimal.

For example, something as simple as 0.1 in binary is like Pi in decimal - it isn't a finite number.

Do math on those converted numbers and there are going to be rounding errors.

If you change the format of K9 to General, you will probably find it's a very small negative number expressed in scientific notation.

Rounding the result in K9 to something greater than 4 to allow for 1440 minutes in a day, should get rid of the error.