Hello all,
I am running into what I believe to be two separate bugs in Microsoft Excel for Office 365 MSO 16.0.10730.20155 64-bit. It seems that Excel does not properly perform calculations or auto-fills properly on time/date data.
I'd like to run it by you guys before I jump through the hoops of submitting it to M$. It's more probable I'm just doing something wrong.
Manual Entry
As you can see below, when dates are entered manually, the raw value is exactly what it should be.
Auto-fill
To generate the auto-fill I manually entered two values in the first two rows exactly 1 hour apart, highlighted them both, and used the auto-fill tool dragging down to the last row. The first auto-filled time is immediately off by -0.01 seconds. Interestingly enough, each consecutive auto-fill afterward is only off by another additional -0.005 seconds, not the initial -0.01.
The first time I performed this, everything looked fine but subsequent calculations using these values were off. It was only when I changed the date/time format to "m/d/yy h:mm:ss.000 AM/PM" did I discover the issue. If the default date/time format is used, the value is rounded to the nearest minute causing everything to look just fine when in actuality the underlying value is not.
Calculation
For a work around I thought I could simply use a formula that adds 1 hour (1/24) to the value above it. Example: H6:=H5+(1/24)
Using this method to increment the date/time seemed to work at first glance. The value out to the millisecond looked accurate. When I calculated the deviation, it fell apart on the 23rd iteration.
Upon closer inspection the raw value actually deviated before the error calculation caught the discrepancy. These are highlighted in yellow. At this point, I don't even think the calculation for the "Error" column can be trusted as it is probably impacted by the same bug.
Questions
Is this a known and "working as expected" limitation of Excel due to it Floating Point math processing?
Can I do something different to keep the deviation from running away more and more as time is iterated forward?
Thanks!
I am running into what I believe to be two separate bugs in Microsoft Excel for Office 365 MSO 16.0.10730.20155 64-bit. It seems that Excel does not properly perform calculations or auto-fills properly on time/date data.
I'd like to run it by you guys before I jump through the hoops of submitting it to M$. It's more probable I'm just doing something wrong.
Manual Entry
As you can see below, when dates are entered manually, the raw value is exactly what it should be.
Auto-fill
To generate the auto-fill I manually entered two values in the first two rows exactly 1 hour apart, highlighted them both, and used the auto-fill tool dragging down to the last row. The first auto-filled time is immediately off by -0.01 seconds. Interestingly enough, each consecutive auto-fill afterward is only off by another additional -0.005 seconds, not the initial -0.01.
The first time I performed this, everything looked fine but subsequent calculations using these values were off. It was only when I changed the date/time format to "m/d/yy h:mm:ss.000 AM/PM" did I discover the issue. If the default date/time format is used, the value is rounded to the nearest minute causing everything to look just fine when in actuality the underlying value is not.
Calculation
For a work around I thought I could simply use a formula that adds 1 hour (1/24) to the value above it. Example: H6:=H5+(1/24)
Using this method to increment the date/time seemed to work at first glance. The value out to the millisecond looked accurate. When I calculated the deviation, it fell apart on the 23rd iteration.
Upon closer inspection the raw value actually deviated before the error calculation caught the discrepancy. These are highlighted in yellow. At this point, I don't even think the calculation for the "Error" column can be trusted as it is probably impacted by the same bug.
Questions
Is this a known and "working as expected" limitation of Excel due to it Floating Point math processing?
Can I do something different to keep the deviation from running away more and more as time is iterated forward?
Thanks!
