convert dd/mm/yyyy to yyymmdd

[cardgunner]

I have a colum of dates (2156 records) where the date is in dd/mm/yyyy format. Also some have the 0 some don't such as 31/06/2012 is 31/6/2012 or 04/02/2012 is 4/2/2012. So I can't do =left(cell, 4) & mid(4,2) & right(2). Is there a function that will convert (dd/mm/yyy) to (yyyymmdd)?

Thanks for you help.
Cardgunner

[Fotis1991]

..So I can't do =left(cell, 4) & mid(4,2) & right(2)

But you can: =DAY(CELL)----=MONTH(CELL)------YEAR(CELL).

Also take a look to TEXT to COLUMNS option.

[benishiryo]

hi Cardgunner, could you select the range & press CTRL + SHIFT + ~
you should see all of them become numbers if they are real dates. then undo it. if they are all numbers, you can simply right-click & format cells -> Custom:
yyyymmdd

if they are not, select the range again. go to Data -> Text to Column -> Delimited -> Next -> Next -> Date: DMY -> Finish. you can now do the same formatting of cells mentioned.

upload a file if it dont work

[cardgunner]

The text to column worked. Not all the fields were real dates by using your ctrl+shift+~.

So it parsed out the dates and put them in the next 3 columns and I can now proceed.

Thanks for the great help.

Cardgunner.