在Excel中转换时间格式 [英] Converting Time Formats in Excel
本文介绍了在Excel中转换时间格式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何将以字符串(21m49s)输出的时间值转换为Excel时间值(00:21:49)?如果没有给出小时值,则不会为单元格分配0h值。
How would I convert time values that are output in as a string (21m49s) into the Excel time value (00:21:49)? If there is no hour value to be given, the cell is not assigned a 0h value. It will display only minutes and seconds in that case.
还需要记录60分钟以上的读数,显示为1h34m14s。
Will also need to account for readings above 60 minutes which would display as 1h34m14s.
这是我如何收到时间值:
This is how I receive the time values:
这是我需要他们看起来像:
This is what I need them to look like:
推荐答案
尝试这样:
=TIME(IF(ISNUMBER(FIND("h",A1)),LEFT(A1,FIND("h",A1)-1),0),IF(ISNUMBER(FIND("m",A1)),IF(ISNUMBER(FIND("h",A1)),MID(A1,FIND("h",A1)+1,FIND("m",A1)-1-FIND("h",A1)),LEFT(A1,FIND("m",A1)-1)),0),IF(ISNUMBER(FIND("s",A1)),IF(ISNUMBER(FIND("m",A1)),MID(A1,FIND("m",A1)+1,LEN(A1)-1-FIND("m",A1)),LEFT(A1,FIND("s",A1)-1)),0))
虽然@Jeeped打败了我,我会发布我的UDF: p>
Though @Jeeped beat me, I will post my UDF:
Function TimeChange(str As String) As Date
Dim strArr() As String
Dim i As Integer
Dim hr As Integer
Dim min As Integer
Dim sec As Integer
str = Replace(str, "h", "h ")
str = Replace(str, "m", "m ")
str = Replace(str, "s", "s ")
strArr = Split(Trim(str))
For i = 0 To UBound(strArr)
Select Case Right(strArr(i), 1)
Case "h": hr = Left(strArr(i), Len(strArr(i)) - 1)
Case "m": min = Left(strArr(i), Len(strArr(i)) - 1)
Case "s": sec = Left(strArr(i), Len(strArr(i)) - 1)
End Select
Next i
TimeChange = TimeSerial(hr, min, sec)
End Function
这篇关于在Excel中转换时间格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文