SOLVED

Excel 2013 Date computation bug

Copper Contributor

Hi,

I can't figure out what's the problem in this situation:

When I calculate the sum of the time spent of the day, with those values: (see attachement)

 

StartEndStartEndTotalFormulaBreakWithout breakFormulaEqualityFormulaControl
08:43:0012:11:0012:41:0017:42:0008:29:00 =(B3 - A3) +  (D3 - C3)00:30:0007:59:00 =E3-G3VRAI =L3=H307:59:00
07:13:0012:42:0013:41:0017:25:0009:13:00 =(B2 - A2) +  (D2 - C2)00:30:0008:43:00 =E2-G2FAUX =L2=H208:43:00



The first line is correct but the second is wrong.

Excel say that 08:43 is not equal to 08:43. But only when the firs 08:43 is calculated by the sum of the day minus the 00:30 min break.

How can I fix this equality problem?

I have double checked, no trim problem, no date format problem. I've used the date validation for the four date inputs.
Even when I print long date format, the year, month, day, hour, minutes, and seconds are equals.

If I add one minute 17:25 -> 17:26 and compare to 08:44 it's solved
If I subtract one minute 17:25 -> 17:24 and compare to 08:42 it's solved.

It seems to be a rounding problem but I haven’t found millisecond in excel.

Thanks for help

 

5 Replies
best response confirmed by Goldridge (Copper Contributor)
Solution

@Goldridge , that is floating point calculation issue. In Excel one minute is 1/24/60, you calculate them as floating point numbers. To fix you may round the time to nearest second

=MROUND(F2-G2,"0:00:01")

 

Goldridge,

 

It would appear that when calculating time excel falls over sometimes in rounding, in this case around the 14th decimal place, as you can see from the attached example. I changed the time serial number to a number format so you can the decimal places and used a Round function for both the time calculation and the time validation value.

 

I think it is just one of quirks of excel. 

Thank you very much it solves that problem!

Thank you too for the details analysis!

 

 

@Goldridge , you are welcome

1 best response

Accepted Solutions
best response confirmed by Goldridge (Copper Contributor)
Solution

@Goldridge , that is floating point calculation issue. In Excel one minute is 1/24/60, you calculate them as floating point numbers. To fix you may round the time to nearest second

=MROUND(F2-G2,"0:00:01")

 

View solution in original post