[Sunnyd204]

I am frustrated with the following problem.
I need to convert the date to US formatting.


I receive a file in .csv format and the dates come as text and a normal date format all within the same column.

Example:

12/4/2012 23:16
12/4/2012 23:21
13/04/12 12:04 AM
13/04/12 12:52 AM
13/04/12 3:20 AM

The top 2, Excel reads as date. The bottom 3 excel reads as text...I need the bottom 3 to read as date.

Please HelP!!!

[20_2_Many]

Are you Importing the file into excel (Data > From Text), choosing Delimited > Next > Selecting Comma as the Delimeter > Next > Changing the Column Data Format for your Date Field to Date and Selecting MDY, DMY, or YMD - whichever you Prefer > Changing any other format you may need in other columns > Finish > Chooing the Cell to beging the Data import from > OK.
?

[Sunnyd204]

The file that I open is a pre-formatted csv file.
In other words, there is no comma delimiting necessary because the data is already split out in to the respective columns.

I have tried the method you suggested, and it did not work.
My guess is I need to be able to convert the UK date formatting to US. As soon as the day is 13 and up it changes it to a text cell rather than a date.

[20_2_Many]

Try this.
I did not mess with the Hours except to get them out of the way.Hope your .csv does not actually come with multiple hour formats!
Run the PrepColumns. It adds 2 columns to hold the Hours and moves hours over. Then Calls the Change to UK macro.
This starts at bottom and works up to Row 2, swapping the Day and Month in each cell.
Test Reset just puts it back to the Date and Hour info from your original post.
Good Luck

[etaf]

i dont think its the 13 - its the AM thats the issue

its because you appear to have a time format in the csv file of 24 hour clock and 12 hour clock mixed

1st two are in 24 hour and last 3 have AM in the field - but i think 20_2_many has replied with a macro to resolve

[Garf13LD]

ermm... its to US not UK
The first 2 dates reads 4 Dec 2012, which are incorrect.
The last 3 reads 4 mth(13) 2012, which are invalid.
Swapping the dd/mm to mm/dd will solve all the problem.
One way is to do a find replace in MS word.
1. open the csv file in word.
2. load up the replace dialog (Ctrl + H)
3. Click more and check use wildcards
4. in the search field use one or the following
([0-9]{2})/([0-9]{2})/(*M) for date format ##/##/##
([0-9]{1,2})/([0-9]{1,2})/(*M) for date format with 1 or 2 digits day mth
5. in the replace field use the following
\2/\1/\3
6. save the file and reopen in excel


if you need it in excel formula or vba macro, let us know.

[20_2_Many]

I probably did swap the splits inadvertently.
ukdate = x(1) & "/" & x(0) & "/" & x(2)
or
ukdate = x(0) & "/" & x(1) & "/" & x(2)
will provide the mm/dd/yyyy or dd/mm/yyyy format whichever is desired.

[Garf13LD]

Excel treat date as numbers, hence after PrepColumns "split", ChangeToUK is not able to swap "real dates"

This formula I have written years back will fix it.
of cos, I will rewrite it into vba when I have time.
=VALUE(MID(TEXT(A1,"DD/MM/YY"),FIND("/",TEXT(A1,"DD/MM/YY"),1)+1,3)&LEFT(TEXT(A1,"DD/MM/YY"),FIND("/",TEXT(A1,"DD/MM/YY"),1))&RIGHT(TEXT(A1,"DD/MM/YY"),2))

[Sunnyd204]

I tried a few of the options suggested and it worked like a charm!

Thanks Everyone

[etaf]

your welcome thanks for letting us know
You can mark your own threads solved using the button at the top of the page of the thread in the upper left corner.