CODEWITHSUNDEEP
stringexceltime
Alex Carrausse
Alex Carrausse

Reputation: 21

Convert a string containing days, hours and minutes into hh:mm:ss

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

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

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?

Upvotes: 1

Views: 10110

Answers (3)

Kamalakannan
Kamalakannan

Reputation: 11

Example: 7 weeks 31 days 24 hours 60 minutes

Formula: 

= ((IFERROR(VALUE(MID(J2,IF((FIND("weeks",A1)-3)=0,1,
(FIND("weeks",A1)-3)),2)),0))*(60*60*7*24))+
   ((IFERROR(VALUE(MID(A1,IF((FIND("days",A1)-3)=0,1,
(FIND("days",A1)-3)),2)),0))*(60*60*24))+
   ((IFERROR(VALUE(MID(A1,IF((FIND("hours",A1)-3)=0,1,
(FIND("hours",A1)-3)),2)),0))*(60*60))+
   ((IFERROR(VALUE(MID(A1,IF((FIND("minutes",A1)-3)=0,1,
(FIND("minutes",A1)-3)),2)),0))*(60))

Upvotes: 1

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

This previous answer of mine will get you part of the way.

A slight adjustment is:

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

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.

enter image description here

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.

enter image description here

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

Upvotes: 3

barry houdini
barry houdini

Reputation: 46331

This formula works for all your examples

=SUM(MID(0&A1&"0000",FIND({"s","m","h","d"},0&A1&"xxsmhd")-2,2)/{86400,1440,24,1})

assuming data in cell A1, format result cell as [h]:mm:ss

It fails if you have a single digit value that isn't at the start, so if you have 12h03min that will be OK but if you have 12h3min the formula will fail. I can probably fix that, though......

Upvotes: 4

Related Questions