CODEWITHSUNDEEP
excel
Sarvi Shanmugham
Sarvi Shanmugham

Reputation: 505

Convert time string from unix time command like 10m20.5s into time format in excel

I have time data from the unix time command like 203m53.5s

I have this data in excel. I want it to be converted to Excell time format so I can do mathematical operations like sum and averages over them.

How can I do this

Upvotes: 0

Views: 72

Answers (2)

Wayne Kaskie
Wayne Kaskie

Reputation: 3483

One way is to use a forumala to strip out the m and s and use those values for time in a new column in Excel.

Assume the Unix data is in column A.

=(LEFT($A1,FIND("m",$A1)-1)*60+MID($A1,FIND("m",$A1)+1, LEN($A1)-FIND("m",$A1)-1)/84600

then format the cell as custom and choose the time without the AM/PM

Breakdown: (get the minutes by finding "m") multiply by 60 to convert to seconds + (get the seconds by starting at the location of m, +1 to the location of m-length of the whole string) -1 to account for the actual "s" Then divide the whole thing by 84600 to convert to time as a decimal

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

Replace the m with : and the s with "":

=--SUBSTITUTE(SUBSTITUTE(A1,"m",":"),"s","")

Now that the time is in a format that Excel will recognize we need to change it from string text to a number. The -- is forcing the string into a number by performing a mathematical process of multiplying -1 * -1 to it.

It can be replaced by TIMEVALUE()

Then format the cell with a custom format of:

[mm]:ss.0

enter image description here

Upvotes: 3

Related Questions