CODEWITHSUNDEEP
excelvba
spittingfire
spittingfire

Reputation: 111

How do I convert text or string to time format using excel vba?

I have a report and in columns L to Y I have the time of hh:mm:ss written as text and as a result I'm unable to do pivot calculations.

Is there an excel vba script that I can use to convert columns L:Y to the time value of hh:mm:ss?

any help would be appreciated.

Thanks

Upvotes: 0

Views: 15886

Answers (2)

Slai
Slai

Reputation: 22886

A bit late, but another similar solution is to copy a blank cell and add it to the range with Paste Special

or better, change the format and copy the values like this:

Set r = Intersect(UsedRange, Range("L:Y"))  ' only the used range in columns L to Y
r.NumberFormat = "HH:mm:ss"    ' military time format 24:59:59 ?
r.Value2 = r.Value2

Upvotes: 1

YowE3K
YowE3K

Reputation: 23994

Without using VBA, you could perhaps put a formula in columns Z onwards, e.g. Z2's formula could say =TIMEVALUE(L2) etc.

Using VBA, you could use either the CDate or TimeValue functions to convert the string to a date/time.

Upvotes: 0

Related Questions