Need help with Date/Time Conversion

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!

No, not really. We won't be saving the processed workbook. We just import the CSV file, run a macro to format it and extract the information we need and then print the result. But I think I got it to work, though admittedly very clumsily. But I guess it will work for now. Thanks anyways for your help.

Thanks so much for you help before. Your code was working great....until we hit the new year. Now the dates are coming out like this:

Original Downloaded format:

Start: 2/1/2014 8:30
End: 2/1/2014 11:30


Transformed format:

Start: 1//2/014 8:30:00 AM
End: 1//2/014 11:30:00 AM

The way it should be is:

Start: 1/2/2014 8:30 AM (or 01/02/2014 8:30 AM)
End: 1/2/2014 11:30 AM (or 01/02/2014 11:30 AM)

Any pointers on how to modify your code so that they work in the new year? I think the problem is that the months now are only 1 digit, so that is throwing things off. The time is also throwing in the seconds which we don't need.

Any suggestions?

I'm sorry. You're right.

Thank you so much! That seems to work perfectly. I tried it for several different dates throughout the year and it seems to be robust. Thank you very much for your help.

Will do. Thanks again.