Time formatting - minutes, seconds and milliseconds

[jvikonen]

Hello,

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

42.336

51.858

01:17.9

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

42.336

51.858

77.900

Or alternatevily

00:42.336

00:51.858

01:17.900

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

[#2]

@jvikonen 

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.

[#3]

@jvikonen 

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.

[#4]

@jvikonen 

As variant that could be

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

on such samle

and apply mm.ss.000 format