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

Question

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

You can copy the dates and paste them in Notepad, then copy the text from Notepad and paste in Excel. — Slai
– Slai, Commented Jul 30, 2016 at 1:52
Thanks slai, doing that works however I think it is impractical to be doing this each time the report is generated. Was hoping for something more automated. — spittingfire
– spittingfire, Commented Jul 30, 2016 at 2:08
Clipboard copy and paste can be automated with MSForms.DataObject. Anyway, you can try something like [L1:Y2].Value = [L1:Y2*1] — Slai
– Slai, Commented Jul 30, 2016 at 2:59

Slai · Accepted Answer · 2016-08-01 14:18:32Z

1

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

answered Aug 1, 2016 at 14:18

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

YowE3K · Accepted Answer · 2016-07-30 02:41:46Z

0

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.

answered Jul 30, 2016 at 2:41

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related