Time formatting - minutes, seconds and milliseconds

Copper Contributor



Any suggestion how to user Format Cells - Custom time format in the following scenario? As example, I have following data imported to excel:




This data should be understood as time durations in format mm:ss.000. I would like to have advice how to get data above to be shown as in format ss.000 like below




Or alternatevily



Hopefully this can be achieved without too complicated formulas etc. Any advices are highly appreciated!

3 Replies


I think a format of

= TEXT(time, "hh:mm:ss.000")

should work, either within a TEXT statement (as shown) or applied directly to the cell as a custom number format.


Your sample data is inconsistent - seconds for the first two entries, and minutes and seconds for the last entry. To get seconds stored as an Excel time value, you'll need to divide them by 86400 (the number of seconds in a day).  Then you can apply a custom format [s].000 or [ss].000 (or [mm]:ss.000 for your alternative).

Note that if your time data has a value like 01:18 (including the colon, but omitting the decimal portion), it will be interpreted as hours and minutes instead of minutes and seconds.  For such a value you would have to divide by 60.


As variant that could be

=IF( B3<1/24, B3,  TIMEVALUE("0:" &  TEXT(B3, "@")) )

on such samle


and apply mm.ss.000 format