Decimal Seconds are being rounded by Excel

[keith2511]

I am using Excel 2013 to enter times in minutes and seconds to one decimal place.

The cells have been formatted using the custom format mm:ss.0

When I enter 1:51.9, the value is displayed correctly in the cell i.e. 01:51.9 but if I edit the cell, 00:01:52 is shown in the formula bar and if I use the contents of the cell in other formulae, LEFT for example, LEFT(cell ref, 2) returns 0.

Any idea what is happening here?

Thanks

 

[mikerickson]

I did the following testing,

Put 1:59:40 in A1 (formula bar shows 1:50:40 AM
Put =a1/60 in E1. (cell shows 0:01:51, formula bar shows = A1/60)
Custom format E to h:mm:ss.0 (cell shows 0:01:50.7)
Copy E1, paste special/values, the cell shows 0:01:59.7, but the formula bar shows 12:01:51 AM.

As a test, I put =E1*60 in another cell and it returns 1:59:40.

The issues isn't with the underlying value (.00128472222222) but with Excel's cell value to Formula bar conversion.

If I use LEFT(E1, ROWS($1:1)) and drag down, I get the series 0, 0., 0.0, 0.00, 0.001, ...
The LEFT is being applied to the unformatted serial time value.

 

[joeu2004]

When I enter 1:51.9, the value is displayed correctly in the cell i.e. 01:51.9 but if I edit the cell, 00:01:52 is shown in the formula bar and if I use the contents of the cell in other formulae, LEFT for example, LEFT(cell ref, 2) returns 0. Any idea what is happening here?

You are correct, and there is nothing we can about it, AFAIK. There are two separate issues.

First, when we edit cells, we edit the cell contents as they appear in the Formula Bar. The Formula Bar presents time only to the second. We cannot see fractional seconds; ergo, we cannot edit them(!). And the Formula Bar rounds fractional seconds to the second. The only work-around that I know is: re-enter the entire time.

Second, when we use LEFT(cellref,...) and any other string function, numeric cell values are always presented in Number or Scientific form, depending on magnitude, regardless of how they appear in the cell. For that reason, I do not use string functions (other than TEXT) with numeric cell references. We never know what we'll get.