converting string in dd/mm/yyyy format to dat in mm/dd/yyyy format

[hy2934]

Hi All,

I am a newbie here and would like to apologize in advance if this question has already been answered before (If yes, please direct me to link as I couldn't find it via search)

My problem: I get regular data that I import in excel. Column A is always date but in dd/mm/yyyy format (my regional date setting is USA), and I wish to create another column (B) that has the corresponding date in mm/dd/yyyy format that I can then export to a different software.

I have tried to copy/paste column A to B, then format cells-> mm/dd/yyyy .. this didnt work. I have even tried to format column A by the method above and it doesnt change anything which leads me to believe that when I import data in excel, it treats it as string (or text)

Any idea?

Thanks

 

[AkaTrouble]

try this (untested)

if import date is in A1 in B1 put

=DATEVALUE(A1)

and format as required

 

[Rent23]

Hi, if the format in columns A is dd/mm/yyyy use:

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

 

[hy2934]

Hi, if the format in columns A is dd/mm/yyyy use:

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

Sorry, it doesn't work. A2 had: 08/09/2013, and when i used your formula in B2, it shows "2/10/3427"

 

[Rent23]

Sorry, unfortunately without seeing the complete set of data it is difficult to guess. Try this

=DATE(RIGHT(TEXT(A2,"mm/dd/yyyy"),4),MID(TEXT(A2,"mm/dd/yyyy"),4,2),LEFT(TEXT(A2,"mm/dd/yyyy"),2))

Tested with a few examples, let me know with your database.

 

[SpillerBD]

When importing (assuming using import from text file Wizard) you should define that column as date (Not General) and define the format. That way the data will import as a date value. Because some values may be out of range for the month expected, Excel is probably switching all to text. Could also be that there are extra spaces included forcing to text (which would also screw up the other listed formulas to convert the data from text to data.)
Is the LEN of each cell ALWAYS 10?