VBA not keeping correct time format

[bdouglas1011]

I have a macro that imports a text file and it has to format and adjust columns to be in the correct order and format for what will happen after that.
I have done this with other files but this one wont work correctly.

Sometimes I have the tab delimited file or they might send it as CSV and I just resave it as tab Delimited

Step 1 - Import the text file as Tab Delimited
step 2- The file adjusts the columns and format.

Problem is in the CSV the date and time is in there properly you just have to reformat to see it correctly in (09/12/14 12:00AM)
Once I import the macro & reformat the column to the proper format the date goes to (01/00/00 12:00 AM)

I know the date is in there properly I can see it when looking at the CSV file. Once I resave the CSV as tab delimited it must lose the date somehow.

Can this be corrected

Or How can I change my import from Text File to CSV file and then reformat. Here is my import Code.

Please Login or Register  to view this content.

Please help I have racked my brain enough

[LJMetzger]

Please upload a sample text file and a sample .csv file, otherwise answers will probably be based on speculation as to the format of your dates.

[bdouglas1011]

I have attached one of each you can see in the CSV the date is proper and then when you import the TXT file the format for the date is gone.
I worked around by saving the CSV as .XLSX

Then importing that file it worked I just dont know why I kept losing the format when saving the CSV as Text file.

[LJMetzger]

Thanks for uploading the files.

they might send it as CSV and I just resave it as tab Delimited

When you save the CSV file as 'tab delimited' it saves the text EXACTLY AS YOU SEE IT in the cells, even though the underlying value may contain additional information. When I opened the CSV file in Excel 2003 Column A (date and time) was formatted for minutes and seconds and fractions of a second only (mm:ss.0). When saving the file as tab delimited text, Column A became mm:ss.0 format (as expected).

If you format Column A to include date and hours in addition to minutes and seconds in a custom format of your choice, that custom format will be reflected in the output file when you save the file as tab delimited text.

Lewis