Possible Excel Bug (2010 & 2013) Hi, one of our users appears to have discovered
ID: 3571074 • Letter: P
Question
Possible Excel Bug (2010 & 2013)
Hi, one of our users appears to have discovered a bug in Excel and I've tested this in 2010 and 2013 and experience the same outcome. It may be present in older version but I don't have these available.
The bug occurs by doing the following:
1. In cell A1 format the cell as custom date format 'dd/mm/yyyy hh:mm', then in the field enter any date and a time of 00:00
2. In cell A2 do the same as above but enter the time as 00:02
3. Now select the two cells and drag to copy to the cells below - drag into the 1000s just as a test.
You should see that the time on the first 100 cells is correct and increments by 2 minutes each time. But at this point it loses a second from the time, if you started at 00:00 it should now be 03:19:59 in cell 101.
This loss of a second also occurs around cell 300, 500, 700, 900, etc.
I'm reluctant to use one of our available support tickets for this issue, as it's only a handful of users that would make use of this. Is there another way I can go about possibly raising this bug with Microsoft?
Thanks fpr help !
Explanation / Answer
Hi..
When you insert a date, Excel actually stores a floating-point value. Calculations with floating-point values cause problems because they are limited in width and their internall representation is binary based (powers of 2).
You can see the floating-point value representation of a date by just right--clicking on a cell where there is a date, Format cells, choose Number and display 15 decimal digits.
Turns out that, because of the above, filling cells with date differences creates calculations that are immediately wrong, not just after row 100. Just after row 100 is when the errors are so big that they appear wide and clear. But let's just say that any calculation results in a number of decimal rounding errors, and if you make more than calculation, the rounding errors add up. In other words, the value in A3 resulting from adding to A2 the delta between A1 and A2 is already wrong at a 10^-5 level.