Excel VBA extracting hours, minutes and seconds from a Duration time stamp

Michael
Michael used Ask the Experts™
on
I wish to extract the hours, minutes and seconds from a duration time stamp for example: 114:35:10

If such a duration is inserted into a cell, excel places a date stamp prior to it.

When I attempt to use:

Hour(string) or format(string,  “[h]:mm:ss”) the result is incorrect.
I also want to be able to extract the mm and ss values.

Can an expert please help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Try it like this...

Hours = Int(StrTimeStamp) * 24 + Hour(StrTimeStamp)
Minutes = Minute(StrTimeStamp)
Seconds = Second(StrTimeStamp)

Open in new window

NorieAnalyst Assistant
Commented:
How is the date stamp being entered in the cell?

If enter 114:35:10 in a A1 manually I can use these formulas to extract the housr, minutes and seconds.

=TEXT(A1, "[h]")+0

=MINUTE(A1)

=SECOND(A1)

Note, the +0 in the first formula is to convert the text value returned by  the TEXT formula to a numeric value.


If you want code try this.
Dim hr As Long
Dim mn As Long
Dim sc As Long

    hr = Split(Range("A1").Text, ":")(0) ' hours
    mn = Split(Range("A1").Text, ":")(1) ' minutes
    sc = Split(Range("A1").Text, ":")(2) ' seconds

Open in new window

Top Expert 2014

Commented:
@Michael

How is the duration cell formatted?
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!

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Norie's code assumes that there will always be hours, minutes and seconds in the timestamp. The following which builds on his code, doesn't.

Dim hr As Long
Dim mn As Long
Dim sc As Long
Dim strParts() As String

strParts = Split(Range("A1").Text, ":")

Select Case UBound(strParts)
    Case 2
        hr = strParts(0) ' hours
        mn = strParts(1) ' minutes
        sc = strParts(2) ' seconds
    Case 1
        hr = 0 ' hours
        mn = strParts(0) ' minutes
        sc = strParts(1) ' seconds
    Case 0
        hr = 0 ' hours
        mn = 0 ' minutes
        sc = strParts(0) ' seconds
    Case Else
        MsgBox "Unexpected timestamp value"
End Select

Open in new window

Author

Commented:
Thanks for everyone responses. You have assisted me in either answering my question through the use of VBA or cell formulas. As for Aikimark comment: as soon as I input a duration the cell is formatted as “[h]:mm:ss”. Thanks for everyone’s input.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Michael!
Top Expert 2014

Commented:
What is the unit(s) of your time duration that you enter?

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