Looking for an Excel formula to convert time.

Mike Satterfield
Mike Satterfield used Ask the Experts™
on
I have a report in excel where the total time is written like so:

100d2h54min29s

I was wondering / hoping someone had a formala to confert that data into a straight number of seconds.

IE:

8650469

Thank you,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
It would be easy to do that with the help of VBA. Are you open to a VBA solution to have a User Defined Function which you can either use in macros or on the worksheet itself as a regular formula?

Author

Commented:
Yes i would be open to that.
Rob HensonFinance Analyst

Commented:
What happens to the string if an element is zero?

Does it still show in the string, eg 0h or will that element be omitted?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rob HensonFinance Analyst

Commented:
Try this formula, where string is in A4:

=MID(A4,1,FIND("d",A4,1)-1)*86400+MID(A4,FIND("d",A4,1)+1,FIND("h",A4,1)-FIND("d",A4,1)-1)*3600+MID(A4,FIND("h",A4,1)+1,FIND("m",A4,1)-FIND("h",A4,1)-1)*60+MID(A4,FIND("m",A4,1)+3,2)*1

Assumes elements with zero value will still be included.
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Place the following Function on a Standard Module like Module1.

Function ConvertToSeconds(ByVal Str As String) As Long
Dim Matches      As Object
Dim TotalSeconds As Long
Dim arrDurations As Variant
Dim duration     As Variant

arrDurations = Array("d", "h", "min", "s")

With CreateObject("VBScript.RegExp")
    .Global = False
    For Each duration In arrDurations
        .Pattern = "\d+(?=" & duration & ")"
        If .Test(Str) Then
            Set Matches = .Execute(Str)
            
            Select Case duration
                Case "d"
                    TotalSeconds = TotalSeconds + Matches(0) * 86400
                Case "h"
                    TotalSeconds = TotalSeconds + Matches(0) * 3600
                Case "min"
                    TotalSeconds = TotalSeconds + Matches(0) * 60
                Case "s"
                    TotalSeconds = TotalSeconds + Matches(0)
            End Select
        End If
    Next duration
End With
ConvertToSeconds = TotalSeconds
End Function

Open in new window

And then assuming your string "100d2h54min29s" is in A2, try this formula on the Sheet...
=ConvertToSeconds(A2)

Open in new window


Please refer the attached for more details...
ConvertToSeconds.xlsm
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Assuming that d, min and h are all present, you may use:
=86400*(LEFT(A2,SEARCH("d",A2)-1)+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A2,SEARCH("d",A2)+1,99),"h",":"),"min",":"),"s",""))

Open in new window

Rob HensonFinance Analyst

Commented:
@byundt - nice logic, convert the days and then convert the remaining string into a proper time stamp.

Author

Commented:
Thank everyone who participated!

I really appreciate the assistance.
Rob HensonFinance Analyst

Commented:
Glad to help, can you confirm whether the string will have all elements even if 0 value?

If the elements will still be included, even if zero, then the formula options would work. If not then you are right to choose the UDF option from Neeraj as the formula options would not work or would give incorrect result in those cases.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Mike! Glad I could help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial