将包含天，小时和分钟的字符串转换为hh:mm:ss [英] Convert a string containing days, hours and minutes into hh:mm:ss

问题描述

我有400条记录要处理，它们具有以下格式(字符串):

I have 400 records to process and they are in the following formats (string):

3h
24h20min
3h
2d
26min
1h12min
17h35min
6h12min
30s

我如何制作一个可以自动检测 d ， h ， min 和 s 的公式，并且转换为正确的 hh:mm:ss ，并且hh最终高于24?

How do I make a formula that would automatically detect the d, h, min and s and convert into the right hh:mm:ss with hh being eventually higher than 24?

推荐答案

此稍作调整是

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"d"," "),"h",":"),
 "min",":"),"s",""))

然后将单元格的格式设置为 [h]:mm:ss ，其中 [h] 表示允许的小时数大于24(而不是四舍五入)).

And then format the cell as [h]:mm:ss, where [h] means allowing numbers of hours greater than 24 (rather than wrapping around to zero).

我并不是说此公式适用于您的所有情况.实际上，当您只有几分钟，一个秒，几天和几分钟但没有小时等时，它会失败.但是，您需要帮助或线索"，这确实应该为您设计一个合适的公式提供一个起点.根据您的情况.

I am not claiming that this formula will work on all your cases. In fact, it fails when you have minutes alone, seconds alone, days and minutes but no hours, etc. But, you ask for "help or clues", and this should indeed give you a starting point for devising a formula that is appropriate for your circumstances.

编辑，该死，我无法抗拒.我制作了一个VBA用户定义的函数来解析您的日期字符串.它非常健壮，可以用于所有示例以及更多示例-甚至包括带有随机字符的字符串，例如 6d 243min + 7s .请注意，您仍然必须将单元格的格式设置为 [h]:mm:ss .

EDIT Arrrrrhhhh, I couldn't resist. I made a VBA user-defined function that parses your date strings. It is quite robust and works for all your examples and more -- even string with random characters thrown in, e.g. 6d 243min + 7s. Note that you still have to format the cells as [h]:mm:ss.

Function ParseDateTime(sTime As String) As Date

    Dim i As Long
    Dim identifierPos As Long
    Dim iTimeUnit As Long
    Dim nTimeUnit As Long
    Dim timeUnitCount As Long
    Dim timeUnitIdentifier() As String
    Dim timeUnitDateValue() As Date
    Dim thisDate As Date

    ' What are we looking for in the string?
    ReDim timeUnitIdentifier(1 To 4)
    timeUnitIdentifier(1) = "d"
    timeUnitIdentifier(2) = "h"
    timeUnitIdentifier(3) = "min"
    timeUnitIdentifier(4) = "s"

    ' What does each of these identifiers mean?
    ReDim timeUnitDateValue(1 To 4)
    timeUnitDateValue(1) = 1 ' value of 1 means 1 day in Date type.
    timeUnitDateValue(2) = TimeSerial(1, 0, 0)
    timeUnitDateValue(3) = TimeSerial(0, 1, 0)
    timeUnitDateValue(4) = TimeSerial(0, 0, 1)

    nTimeUnit = UBound(timeUnitIdentifier)

    ' Treat each time unit separately
    For iTimeUnit = 1 To nTimeUnit
        ' Try to locate this time unit's identifier
        identifierPos = InStr(sTime, timeUnitIdentifier(iTimeUnit))
        If identifierPos > 0 Then
            ' Found it. Extract the digits that precede the identifier.
            For i = identifierPos - 1 To 1 Step -1
                If Not (Mid(sTime, i, 1) Like "[0-9]") Then
                    Exit For
                End If
            Next i
            timeUnitCount _
                = CLng(Mid(sTime, i + 1, identifierPos - i - 1))
            thisDate = thisDate _
                + timeUnitCount * timeUnitDateValue(iTimeUnit)
        Else
            ' Identifier not found. Do nothing.
        End If

    Next iTimeUnit

    ParseDateTime = thisDate
End Function

这篇关于将包含天，小时和分钟的字符串转换为hh:mm:ss的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！