Forum Discussion
Format column of text data to time
I have a column of text data in the form of "1234.789" that I would like to convert to to a time format such as: 12 (minutes) : 34 (seconds) . 789 (milliseconds). I tried using a custom format but, was unsuccessful. Any ideas out there? I'm using Microsoft Office Pro 2019.
2 Replies
You cannot do that by formatting the column. Use formulas instead.
Let's say the values are in A2 and down.
In B2:
=QUOTIENT(A2,100)/1440+MOD(A2,100)/86400
Apply the custom number format [m]:ss.000 to B2, then fill or copy down.
Hans, thanks for your suggestions. I used number of formulas, taking groups of 2 characters, adding ":", to come up with the form "mm:ss.sss", then formatted the column as "time".
