Text to Columns Time Issue in Macro

[mpersico]

I have data that is formatted like so...

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]calldate[/TD]
[/TR]
[TR]
[TD]12/12/2015 18:52[/TD]
[/TR]
[TR]
[TD]12/12/2015 16:50[/TD]
[/TR]
[TR]
[TD]12/12/2015 16:17[/TD]
[/TR]
[TR]
[TD]12/12/2015 14:00[/TD]
[/TR]
[TR]
[TD]12/12/2015 13:44[/TD]
[/TR]
[TR]
[TD]12/12/2015 13:35[/TD]
[/TR]
[TR]
[TD]12/12/2015 13:11[/TD]
[/TR]
</tbody>[/TABLE]


I'm running a macro, and one part of that macro is to separate the date/time column into date and time using text to columns. When I manually perform text to columns, my data comes out like so...

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]18:52:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]16:50:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]16:17:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]14:00:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]13:44:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]13:35:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]13:11:00[/TD]
[/TR]
</tbody>[/TABLE]


When I run the same thing in a macro, my data comes out like this...

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]06:52:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]04:50:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]04:17:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]02:00:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]01:44:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]01:35:00[/TD]
[/TR]
[TR]
[TD]12/12/2015[/TD]
[TD]01:11:00[/TD]
[/TR]
</tbody>[/TABLE]


I'm in Excel 2010. When I am doing text to columns, I'm using the delimited option, and setting the delimiter to space. I've set my Windows date format to short date HH:mm, and long date HH:mm:ss, however I don't want to use that option because this is for cleaning data in a work setting and I don't want others to have to adjust their settings on Windows in order to use the macro. Also, that option isn't working for the macro anyways. I'm cleaning approximately 35,000 lines of data or more in each batch, so it can't be done by hand in a reasonable sense. I don't currently have Visual Studio installed on this machine, however I will be getting it soon so I can work with that more. I don't have a lot of experience with writing VB code for Excel spreadsheets, but I have a passing familiarity with VB.

 

[Joe4]

It may just be the format of your Time column (note that the times look the same, except they are PM, i.e. 18:52 is equal to 6:52 PM).
What time format on you using on the converted Time column?

It might also be helpful if you paste your macro code.

 

[mpersico]

I'm working on getting Visual Studio put on my computer so I can paste up the macro code. The problem is that when it's translated over to 01:11:00, it's then assigning "AM" to all of those columns.

 

[Joe4]

Is this data in an Excel sheet?
If so, why are you using VB in Visual Studio for Text to Columns instead of just using Excel's Text to Columns (with or without Excel's VBA instead of VB)?