Pull Month, Year, Time from String

[Lonney]

Hello All,

I have a sheet which captures the below date/time stamp. I need a function to pull the month and time in a 12 hour format and place this data into another column. I can code this via a cell (=MONTH(A2)), but need it to be written as a function in VB. I have been searching the forum, but am unable to locate the solution.

Example Date/Time Stamp:
9/19/2018 18:53

Thanks,

Lon

[protonLeah]

I don't understand why you need a function to return the month when you can use MONTH() in formulas and VBA?

Please Login or Register  to view this content.

[Lonney]

Hello Ben,

I need this to be coded in VB as a loop that breaks out the date and time and place this data into separate columns. Hope this makes sense.

[protonLeah]

You'll have to mod for output cells:

Please Login or Register  to view this content.

[Lonney]

I am unable to get the above code to work. I put together the below code as an example, but am unable to get this to work correctly.

Sub dts()
Dim x As Long, LR As Long

LR = Worksheets("iPad Tracker Raw Data").Cells(Rows.Count, "A").End(xlUp).Row

With Sheets("iPad Tracker Raw Data").Range("a2:a" & LR)
m = Month(Range("J2:J").Value)

End With
End Sub

[bakerman2]

In which column are the Date/Timestamps and in which columns do you want the results displayed ?

[Lonney]

Hello,

Column "J" would be the month and column "I" would be the year.

Thanks for the help.

Lon

[bakerman2]

Please Login or Register  to view this content.

[Lonney]

Thank-you,

I am getting an error as follows: Object Required. It stepped into the code via debug, and it fails at this point:
For Each cl In .Range("a2:a" & LR)

Thanks for your help,

Lon

[Phuocam]

Try:

Please Login or Register  to view this content.

[protonLeah]

Please Login or Register  to view this content.

[Lonney]

Works perfect! Thankyou!

Lon