Change Data into Times via macro...

[Hyflex]

How is the best way to change my data into a time.. I have no idea how to do this

Raw Data:

Please Login or Register  to view this content.

The end result I want is:

Please Login or Register  to view this content.

[nathansav]

Not sure you'd need code, but in code i'd do
Function convert_time(strTime As String) As Date

Dim intHours As Integer
Dim intMins As Integer
Dim intDividerLocation As Integer
Dim intStringLength As Integer

intDividerLocation = InStr(1, strTime, ":")
intStringLength = Len(strTime)

intHours = CInt(Mid(strTime, 1, intDividerLocation - 1))
intMins = CInt(Mid(strTime, intDividerLocation + 1, (Len(strTime) - intDividerLocation)))

convert_time = TimeSerial(intHours, intMins, 0)

End Function

[tigeravatar]

Hyflex,

Formula solution:
=TEXT(SUBSTITUTE(A1,"-",":")&" pm","hh:mm")

And then you can Copy -> Paste Special -> Values.

If you need VBA, you can convert all items at once using the below code. Just change DataCol to be the column (or columns) that contain the raw data you want to convert:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar