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

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.
MichaelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try it like this...

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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

aikimarkCommented:
@Michael

How is the duration cell formatted?
Determine the Perfect Price for Your IT Services

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

Martin LissOlder than dirtCommented:
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

MichaelAuthor 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 ExpertCommented:
You're welcome Michael!
aikimarkCommented:
What is the unit(s) of your time duration that you enter?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.