weird number entry calculation

**stvsteve** · 11-21-2012, 08:44 AM

Hi there!

I have a bunch of timedata in the format mm:ss:iii (minutes, seconds, milliseconds) strangely whenever I enter them in this format excel does some really weird calculations with that and replaces the timestamp with some strange number.
for example:
01:23:456
turns to
0,062916667
This erases the original value (so there is no formula or something left, just the strange number).

How can I stop this nonsense or reformat those timestamps so I can use them in calculations?

thanks in advance!

best regards
stefan

**6StringJazzer** · 11-21-2012, 12:03 PM

Welcome to the Forum!

Putting milliseconds after a colon is not standard notation. Normally you would use 01:23.456. The number you are seeing is Excel's attempt to try to makes sense out of what you entered. The number you see is how Excel internally represents time. In your example, this number represents 1:30:36 (h:mm:ss). I don't know how Excel arrived at that number, though.

To handle this data correctly, use a 01:23.456 format for your input data and set the cell format to Custom, "mm:ss.000"