Problem with dates

[LittleStalky]

Hi,

I have a spreadsheet of data which I've exported from our customer database. Everything is fine except for the dates, which have come through as a mixture of British format dd/mm/yyyy and American format mm/dd/yyyy. The thing I've noticed is that any dates which fall within 2011 have come through in the British format and any that fall within 2012 have come through in the American format. I have about 350 lines of data which I need to mail merge and I don't want to have to go through and change everything manually. Is there a way that I can convert these days to the British date format?

Many thanks

LS

 

[Dharris144]

All you have to do is highlight all the date cells right click then format cell.

Choose the date format you want from the list.

 

[LittleStalky]

All you have to do is highlight all the date cells right click then format cell.

Choose the date format you want from the list.

I have already selected the date format and this does not work

 

[VoG]

Assuming that the dates were originally in American format try this. Select the dates, Data > Text to Columns, click Next twice, tick Date, select MDY then click Finish.

 

[LittleStalky]

Assuming that the dates were originally in American format try this. Select the dates, Data > Text to Columns, click Next twice, tick Date, select MDY then click Finish.

This didn't work either but I think I have it fixed

It turns out that the dates coming through in American format just needed a clean and a trim. I used the isnumber formula to find out which dates weren't technically numbers and then I used the clean and trim formula to tidy up the data. This fixed things right up.

Thank you everyone for your suggestions