Different date formats

[samshan143]

Hi,

I have a worksheet where in a column, dates are stored in various date format i.e. it may be DMY or MDY or YMD.

08-06-12
08-06-12
08-07-12
08-07-12
13/08/2012
13/08/2012
13/08/2012
14/08/2012

<colgroup><col></colgroup><tbody>
</tbody>

Above is just for an example, above dates are for the month of August, but as can been some cells are in MDY formate and some in DMY.

Can i have formula to convert different date formats into one date format?

Thx

 

[ps1]

Why have a formula when you can format the cells?

 

[samshan143]

format cell doesn't help, like in case i want to chnge 08-06-12 into 06-Aug-12, but instead it changes into 08-Jun-12

 

[Andrew Poulsom]

Select your dates and choose Data|Text To Columns. At Step 3 of the Wizard choose Date|MDY and click Finish.

 

[samshan143]

even tht doesn't help, i have alrdy tried tht.....

 

[Andrew Poulsom]

It works for me. I tested it before posting.

 

[samshan143]

i can undrstnd tht,i wanted to attach my sheet to make u undrstnd, but could not.... may be u hv specifically selected tht cell which needs to be converted,since i hv long data, it is very difficult to identify each & every cell & select for text to column.

 

[Andrew Poulsom]

Can't you just select them all?

 

[samshan143]

no as i told, since the data is big & dates are for the whole year, so it is difficult to identify whthr it is really June or Aug (in case of 08-06-12, i told earlier) so in such a case if i select thm evn June (which will look same i.e. 08-06-12 i.e. 08-Jun-12) will get converted into Aug

 

[Andrew Poulsom]

Select the entire column and use Text To Columns. Excel will ignore the dates that are already correct.