Changing date format

[jorgiev]

Hello, I am importing information from an american file. This file has american dates (mm/dd/yy) and I would like to convert this to the european date (dd/mm/yy).
How should I do this?
Thanks!!!

[Tom Urtis]

Would this work for you? Try the formula
=TEXT(A2,"dd/mm/yy")
Modify for cell(s) of interest.

[JayKay]

Hi jorgiev

The previous method will only work if Excel is still 'seeing' the entries as a date data type, in which case you would only need to change the column format to 'DD/MM/YY' from 'MM/DD/YY'. If importing your data via the text imput wizrd you should be 'tell' excel that this cvolumn has a date data type and what format it is in step 3 of the wizard.

If the data is coming in as text then there are two methods, one which gives a text result and one which gives a date result:

For a 'Text' result (Assuming the target is always 8 character text string): =DATE(INT("20"&RIGHT(A29,2)),INT(LEFT(A29,2)),INT(MID(A29,4,2))) This will work for all dates.

For a 'Date' result (Again, assuming an 8 char text string): = =DATE(INT("20"&RIGHT(A29,2)),INT(LEFT(A29,2)),INT(MID(A29,4,2))). This will only work for dates post 2000 onwards but you can alter it for 1900 dates (change "20" dor "19" , but you will need to seperate the two types out and use a different formula for each)

Cheers

JayKay

[JayKay]

Sorry the first formula should have been:

= MID(A29,4,2) & "/" & LEFT(A29,2) & "/" & RIGHT(A29,2)

Both formula assume your data starts in cell A29.

JayKay