I have time data from the unix time command like 203m53.5s
我有来自unix time命令的时间数据,如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.
我在excel中有这些数据。我想将它转换为Excell时间格式,这样我就可以对它们进行数学运算,例如求和和平均值。
How can I do this
我怎样才能做到这一点
2 个解决方案
#1
3
Replace the m with : and the s with "":
将m替换为:和s替换为“”:
=--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.
现在,时间采用Excel将识别的格式,我们需要将其从字符串文本更改为数字。 - 通过执行将-1 * -1与其相乘的数学过程将字符串强制转换为数字。
It can be replaced by TIMEVALUE()
它可以被TIMEVALUE()取代
Then format the cell with a custom format of:
然后使用自定义格式格式化单元格:
[mm]:ss.0
#2
0
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.
一种方法是使用forumala去掉m和s,并在Excel的新列中将这些值用于时间。
Assume the Unix data is in column A.
假设Unix数据在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
然后将单元格格式化为自定义,并选择没有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
细分:(通过查找“m”得到分钟)乘以60转换为秒+(从m的位置开始获取秒数,+1到整个字符串的m长度的位置)-1到帐户对于实际的“s”然后将整个事物除以84600以将时间转换为小数
#1
3
Replace the m with : and the s with "":
将m替换为:和s替换为“”:
=--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.
现在,时间采用Excel将识别的格式,我们需要将其从字符串文本更改为数字。 - 通过执行将-1 * -1与其相乘的数学过程将字符串强制转换为数字。
It can be replaced by TIMEVALUE()
它可以被TIMEVALUE()取代
Then format the cell with a custom format of:
然后使用自定义格式格式化单元格:
[mm]:ss.0
#2
0
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.
一种方法是使用forumala去掉m和s,并在Excel的新列中将这些值用于时间。
Assume the Unix data is in column A.
假设Unix数据在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
然后将单元格格式化为自定义,并选择没有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
细分:(通过查找“m”得到分钟)乘以60转换为秒+(从m的位置开始获取秒数,+1到整个字符串的m长度的位置)-1到帐户对于实际的“s”然后将整个事物除以84600以将时间转换为小数