Date and format problem!

[codonnell]

This problem has been killing me - and a BIG thanks to anyone who can help figure this out....

I have a third party application that exports dates in European format (DD/MM/YYYY) and I want to format as US (MM/DD/YYYY). To make matters worse - the format is inconsistent and sometimes appears as D/MM/YYYY.

Using format cells won't work - example: My cell contains 06/02/07 and if I use format cell to change to 'DD-MMM' the output will read as June 2 and not February 6 (the real value I want).

It would seem I need a solution that rearranges the data. Is it possible to have excel rearrange the data - perhaps by the position of the '/'? Example: swap 06/02/07 for 02/06/07. The solution would need to apply to 6/02/07 as well.

One other related problem: Some of these date cells are in a 'general format'. Even after applying a format change so that is numeric and in date form I cannot manipulate the cell format - it reads the same no matter what I do. Example: A2 = 30/01/07 and is in general format. After ANY format change it reads as 30/01/07.

Raw data is attached. Please help!!

Christopher

[olasa]

This is a simple way of converting the text to Excel data value:
=DATE(2000+RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

If there are blanks at the beginning or the end, use: =TRIM(A1)

Hope it helped
Ola

[daddylonglegs]

Hello Christopher,

how are you importing the data? You might be able to specify the format when you import using the import wizard.

Once you have data in the spreadsheet you should be able to convert to dates using this formula in B1 copied down

=IF(ISTEXT(A1),DATE(RIGHT(A1,4),LEFT(RIGHT(A1,7),2),LEFT(A1,FIND("/",A1)-1)),DATE(YEAR(A1),DAY(A1),MONTH(A1)))

format as required, e.g. m/d/yy

If you want to replace the original column with the corrected dates then copy column B and use Edit > Paste Special > values to paste into column A

[codonnell]

Daddy - your formula worked great with cells that were properly formatted as date - but I have many cells that contain a date but in general format. No matter what I do I can't change these cells into a date format.

Sample attached

Thanks all for the quick help!

[olasa]

Here is the file, with all dates converted.
It worked here, but I had to change ÅÅ to YY for you, so I hope it works.
//Ola

[codonnell]

Thanks to Ola and Daddy - I'm all hooked up!!

Christopher