Forum Discussion
Excel 2013 Date computation bug
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)
| Start | End | Start | End | Total | Formula | Break | Without break | Formula | Equality | Formula | Control |
| 08:43:00 | 12:11:00 | 12:41:00 | 17:42:00 | 08:29:00 | =(B3 - A3) + (D3 - C3) | 00:30:00 | 07:59:00 | =E3-G3 | VRAI | =L3=H3 | 07:59:00 |
| 07:13:00 | 12:42:00 | 13:41:00 | 17:25:00 | 09:13:00 | =(B2 - A2) + (D2 - C2) | 00:30:00 | 08:43:00 | =E2-G2 | FAUX | =L2=H2 | 08: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
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")
5 Replies
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 too for the details analysis!
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")
Thank you very much it solves that problem!
Goldridge , you are welcome
