Date/Time Auto-Fill & Calculation Errors

JesseWV

New Member
Joined
May 15, 2014
Messages
4
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!

Screenshot-4.png
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
excel works to 15 significant digits, always been that way
 

Watch MrExcel Video

Forum statistics

Threads
1,122,780
Messages
5,598,038
Members
414,205
Latest member
Tushark

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top