在 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.

还需要考虑将显示为 1h34m14s 的 60 分钟以上的读数.

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:

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屋！