Date Formatting Issue US to UK Pulling my hair out

[alethea2000]

I have all these dates that have come over in American format and they will not turn into uk format. I have tried doing text to column and just changing the format but nothing. if anyone can get them into uk format i'll kiss your feet.

10/06/2005
06/19/2005
11/08/2005
10/16/2005
11/09/2005

 

[Crow_23]

Just highlight the column containing the date, right click, Format Cells, go down to the bottom and click on Custom. In the box that says "Type" just put in the format that you want. dd/mm/yyyy

Ed

 

[alethea2000]

hi this works for some but not others it looks like some of the dates have come over in date format so they change but something like 06/19/2005 has come over as general so i can't budge it any other ideas please

 

[Crow_23]

Highlight the row and format everything as a date before doing the custom. This way everything is in the same format. The US way is sooooo much better anyway haha

 

[NBVC]

As far as I know, a date formatted as General will return the 5 digit Excel code for that date, eg. 38522. If you click on one of those dates is there an apostrophe in front? If so, you will have to delete that for the date formatting to work.

 

[Crow_23]

Ahh you're right NB. If there are apostrophes in front to get rid of them I think all you would need to do is highlight the column and do a Data-->Text to Columns and then just click on Finish in lower right. That should get rid of the apostrophes. Then format.

 

[NBVC]

Ahh you're right NB. If there are apostrophes in front to get rid of them I think all you would need to do is highlight the column and do a Data-->Text to Columns and then just click on Finish in lower right. That should get rid of the apostrophes. Then format.

That didn't work for me