Different Date Formats

[Jewnaa]

Dear Excel Geniuses

Hi

I have 20K+ rows containing different date formats. I need your help to convert in single format ie. UK/US.

Regards
Jewnaa

 

[Andrew Poulsom]

What different date formats? If all the dates are serial dates, ie they appear as a number when formatted as General, you can apply the same date format to them.

 

[Jewnaa]

Thanks for your reply,
in some rows I have mm/dd/yy or m/d/y or dd/mm/yy or d/m/yy etc. I want to convert all dates to dd/mm/yy

 

[Jewnaa]

some are English Caribbean formats (dd/mm/yy), some are UK (dd/mm/yy) and some are US (mm/dd/yy).

 

[Jewnaa]

these different formats were copied from other excel workbooks, that were containing multiple date formats. I have to convert all to UK (dd/mm/yy) now.

 

[mikerickson]

Back up your file before trying this.
Select the entire column and set the format to what you want. Do all the values match the format that you want?

If the difference is in cell contents rather than just format, my concern is that a cell showing 04/05/13 might mean 4. May or 5.Apr. How is one to know which and (therefore) how to convert to what you want?

 

[Jewnaa]

I have tried to change format ie.

Before
1. dd/mm/yy - 06/02/2001 (02 June 2001)

After
2. mm/dd/yy- 02/06/2001 (02 June 2001)

i need to convert all formats to - dd/mm/yy 02/06/2001 ( 02 June 2001)

system format UK (dd/mm/yy)

Thanks

 

[Andrew Poulsom]

How do you know that the date is 2nd June rather than 6th February?

 

[Jewnaa]

because I have a 2nd column in worksheet in which Month mentioned.

Column A--------------- Column Q
Target Month ----------Target Achieved Date
June 2001 --------------06/02/2001 (02 June 2001)
June 2001 --------------06/07/2001 (07 June 2001)
July 2001 --------------- 08/07/2001 (08 July 2001)

Month mentioned in Column Q is linked with Month in Column A, If A is June then June is also in Q.

 

[Andrew Poulsom]

If that's the case the third entry should be 07/08/2001 to be consistent with the first two.