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

http://stackoverflow.com/questions/20375233/excel-vba-convert-text-to-date — M.M, Jul 30 '16 at 00:40
You can copy the dates and paste them in Notepad, then copy the text from Notepad and paste in Excel. — Slai, Jul 30 '16 at 01: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, Jul 30 '16 at 02:08
Clipboard copy and paste can be automated with `MSForms.DataObject`. Anyway, you can try something like `[L1:Y2].Value = [L1:Y2*1]` — Slai, Jul 30 '16 at 02:59
@Siddharth Rout I can't try it, it probably has issues with blank cells, and the range is not auto extended to the last row. — Slai, Jul 30 '16 at 12:42

score 1 · Answer 1 · answered Aug 01 '16 at 14:18

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

score 0 · Answer 2 · answered Jul 30 '16 at 02:41

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 '16 at 02:41

YowE3K

23,852
7
26
40

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

2 Answers2