<div>
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?
</div>


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?

<div>
Yes i would be open to that.
</div>


<div>
What happens to the string if an element is zero?<br />
<br />
Does it still show in the string, eg 0h or will that element be omitted?
</div>


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?

<div>
Try this formula, where string is in A4:<br />
<br />
=MID(A4,1,FIND(&quot;d&quot;,A4,1)-1<wbr />)*86400+MI<wbr />D(A4,FIND(<wbr />&quot;d&quot;,A4,1)+<wbr />1,FIND(&quot;h&quot;<wbr />,A4,1)-FIN<wbr />D(&quot;d&quot;,A4,1<wbr />)-1)*3600+<wbr />MID(A4,FIN<wbr />D(&quot;h&quot;,A4,1<wbr />)+1,FIND(&quot;<wbr />m&quot;,A4,1)-F<wbr />IND(&quot;h&quot;,A4<wbr />,1)-1)*60+<wbr />MID(A4,FIN<wbr />D(&quot;m&quot;,A4,1<wbr />)+3,2)*1<br />
<br />
Assumes elements with zero value will still be included.
</div>


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.

Sr. Systems Administrator

Mike Satterfield

ConvertToSeconds.xlsm

Finance Analyst

Rob Henson

<div>
Assuming that d, min and h are all present, you may use:<br />

<pre><code id="code-20-42961899-1">=86400*(LEFT(A2,SEARCH(&quot;d&quot;,A2)-1)+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A2,SEARCH(&quot;d&quot;,A2)+1,99),&quot;h&quot;,&quot;:&quot;),&quot;min&quot;,&quot;:&quot;),&quot;s&quot;,&quot;&quot;))</code></pre>
</div>


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",""))

<div>
@byundt - nice logic, convert the days and then convert the remaining string into a proper time stamp.
</div>


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

<div>
Thank everyone who participated!<br />
<br />
I really appreciate the assistance.
</div>


Thank everyone who participated!

I really appreciate the assistance.

<div>
Glad to help, can you confirm whether the string will have all elements even if 0 value?<br />
<br />
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.
</div>


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.

<div>
You're welcome Mike! Glad I could help.
</div>


<div>
<div class="content wysiwyg-content">
I have a report in excel where the total time is written like so:<br />
<br />
100d2h54min29s <br />
<br />
I was wondering / hoping someone had a formala to confert that data into a straight number of seconds. <br />
<br />
IE:<br />
<br />
8650469<br />
<br />
Thank you,
</div>
</div>


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,

Looking for an Excel formula to convert time.

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Microsoft Office

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.