VBA to split date and time

Hi All,

I have a spreadsheet where one cell has the date and time i.e 23/11/2014 13:00:00,

I set up a VBA to split this (using text to columns) which worked the first time when recording the initial macro splitting the information into two cells one with the date and one with time time, however when this is run from the developer tab for further instances it now splits the cell into 3 columns (date, time and AM or PM).

I do not need the am or pm column can anybody help?

Use Excel's text to columns function. On the final stage of the process, you need to choose date for both columns and then format column A as date

Hi,

thanks for the quick reply,

I have just tried the above when I do this the first time recording the VBA it works fine and just splits into two columns, however when I try this again with the VBA I have recorded it splits the cell into 3 columns overwriting some other data in the 3rd cell?

If the format are different in each line, yes it can happen. The only way to resolve is to see what you data look like. Please attach a sample.

hi.

assuming A1 houses the date, try this:

Please Login or Register  to view this content.

Originally Posted by mancubus

hi.

assuming A1 houses the date, try this:

Please Login or Register  to view this content.

Good, but you need to incorporate it on a loop as the OP has lots of rows.

Hi,

thanks for this the above VBA code worked, how would I put this on a loop to include any row along the column (apologies for all the questions I am fairly new to VBA)

It is code mancubus' code

Assuming you have a header in row 1

Please Login or Register  to view this content.

fantastic thank you both for your help it is much appreciated

you are welcome.

reading "one cell" in the first sentence, i assumed the OP needed a pointer.

cheers.

Hello,
I have opened a new thread but I found something useful here for my case. But mine is a bit different. Could you please help me to solve my issue as well.
This is my thread link.