Subtracting Times

I am making a timesheet but having trouble with subtracting times.

I have subtracted 3 times away (start time / lunch break / end time) to calculate hours worked.

I have then taken this from the Contracted hours to see + / - time worked.

However when you look at the calculation of the + / - time worked as a number, there is a a variance at the 16th number past the decimal point when they should be exactly the same (for 07:35 - 07:35). This causes the timesheet to display + and - values for 00:00.

Please see attached excel file on the Example sheet dates 26/01/2011 to 27/01/2011. I have no idea why there is this discrepancy.

Many thanks.

Why do you require it to display anything if it should be resulting in 0? can you not just substitute 0 for ""

I have no idea why there is this discrepancy.

The reason for the discrepancy is because computers (this is not Excel specific) perform calculations in binary while we use decimals and transferring back and forth can lead to discrepancies out around the 15th decimal. You could get around it by using the ROUND function at specific points.

Originally Posted by ChemistB

The reason for the discrepancy is because computers (this is not Excel specific) perform calculations in binary while we use decimals and transferring back and forth can lead to discrepancies out around the 15th decimal. You could get around it by using the ROUND function at specific points.

you could just round

=round((a1-b1)*86400,0)/86400 which will round to the nearest second

or round((a1-b1)*1440,0)/1440 which will round to the nearest minute!

Should solve your problem