Mar 22 2019 03:06 AM
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
Mar 22 2019 08:29 AM
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")
Mar 22 2019 08:59 AM
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.
Mar 26 2019 05:16 AM - edited Mar 26 2019 05:18 AM
Thank you very much it solves that problem!
Mar 22 2019 08:29 AM
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")