Excel calculation quark for 0.89142

[ssentman]

Here's a weird one that I'm hoping someone can resolve or suggest a workaround. I'm using date and time stamps to match certain rows of data across multiple sheets. It's fairly straightforward and works fine for thousands of rows of data with one exception.

As further detail, the date and time are entered as values and the cells formatted to display as mm/dd/yyyy hh:mm:ss. The way I derive the date and time is by adding a date value to a time value. With a few thousand rows of data matching up with no problem, I'm having an issue with one particular time, namely 10/01/2015 21:23:39. The serial date-time for this is 42278.89142. The problem is that whenever I add any whole number to 0.89142 it changes to x.8914199999. This doesn't match the results in the other sheets, which properly have the serial-date time as 42278.89142. I've even tried to round the result of adding 42278 + .89142 to the 5th decimal, but it still results in .8914199999.

Excel 2013 (15.0.4763.1002) MSO (15.0.4763.1003) 64-bit

 

[Jeffrey Mahoney]

I don't get that problem
Excel 2010 (14.0.7145.5000) 32 bit

 

[MarcelBeug]

Even if you enter 42278.89142 in a cell manually, Excel adjusts it to 42278.8914199999. Most probably because Excel uses binary numbers.

However, 10/01/2015 21:23:39 equals 42278.8914236111 whereas 42278.89142 equals 10/01/2015 21:23:38.688.
So I guess this is the real problem.

 

[MarcelBeug]

The rounding to ......1999999 occurs for numbers >=1024, so 1023.89142 = 1023.89142 whereas 1024.89142 becomes 1024.89141999999.

 

[Jeffrey Mahoney]

After entering a time of 21:23:39 in a cell, copying and pasting to value, expanding the format to show more decimal places, I get 0.891423611111111

 

[ssentman]

I didn't realize that Excel changed 42278.89142 when I entered it into a cell manually until you asked, but it does change it even in that scenario. This is the case on the desktop version and Excel Online. This is without consideration of any date-time format issues.

Just type 42278.89142 into Excel Online and look at the cell contents. It is 42278.8914199999.

 

[ssentman]

Yup. That's what I'm finding too. I would think that I could resolve the issue using the Round function to force Excel to cut off the result at the 5th decimal place, but it doesn't work. It still results in .89141999999.

 

[Jeffrey Mahoney]

If you enter it in as a time (21:23:39) it remains as .89142
if you change the format to time in the cell. enter the value 0.89142 it will be 21:23:39

 

[ssentman]

It looks like the solution is to keep the full date and time together, so I'm going to change my methodology to avoid the 0.89142 problem. Thanks for your input on this!