在Excel中转​​换时间格式 [英] Converting Time Formats in 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屋！