Hello,

I have been struggling with an issue for the past 3 days and after exhaustive research and reading forum threads I've yet to find a solution. So I'm hoping someone here can help me or at least point me in the right direction.

Here's the situation:

I have been working on a Macro that will take a bunch of imported data (in a .csv file) and format it, extract certain data, and create a printable report to show just the data that is needed. That is all done and works fine.

However, there is a problem with the date and time data I am importing. The problem is really with the software I am exporting from I think, but I have no control over that, so I'm trying to manipulate the data I get and put it into a workable format.

So here's the problem...the external software I'm using is exporting the date information wrong. So for example, if I export the transactions from today (Nov 10, 2013), they come into Excel as 10/11/2013 (October 11, 2013). I have checked all my system settings, etc. and there are no issued there. And applying a typical date transformation (i.e., formatting it as mm/dd/yyyy h:mm AM/PM doesn't work because later when I try to add the day in front, the day is wrong because the date is really wrong).

I was able to figure out how to create VBA code to take the imported date data (which in my case is Column B), add 3 new columns (C, D, and E) and using the Text to Columns function in Excel, convert the dates to the correct format.

But what I have now looks something like this:

Column B (Original Date): 10/11/2013 8:30 AM
Column C: 11/10/2013
Column D: 8:30
Column E: AM

Now what I want to do (and this is where I am stuck) is:

1) Combine the contents of Columns C, D, and E (producing, for example: 11/10/2013 8:30 AM) into Column F
2) Delete Columns C, D, and E

So that after all this conversion, I"m left just with my original columns but with my date now in the proper format.

To make things a little more challenging, this is something we have to do every day, and every day there are different numbers of rows of data, so I need to write the VBA to dynamically handle the # of rows I have on a give day.

Any suggestions?

Your help is greatly appreciated!