Changing of date format

[sharathnarayanan]

Hello,

Need help to change the date format form 25-12-2014, 26/12/2014 etc,,, to 2014-12-25. Please refer to Column M & N in the attached work sheet.

Thanks in advance...

[FDibbins]

wow, where does this data come from? none of those entries in column M are dates, they are all text that looks like a date, and no formatting will change that.

Also in your expected results, although not a big deal, your 1st result is formatted as yyyy-mm-dd, and the rest is dd-mm-yyyy - which do you want?

OK to keep this simple, in M4, remove that leading space, otherwise the formula becomes more complex, and it looks like that is the only entry with a leading space.

Then use this to extract the date...
=DATE(MID(M4,7,4),MID(M4,4,2),MID(M4,1,2))
Format that as needed.

You will still be left with some errors, because some of your "dates" actually have the month name in them, instead of the month number. I suggest you use filters to ID those and manually correct them - or use Find/Replace

[ganeshkumar]

Hi,
first remove the space in the cell(Date). then select the date range right click -> format cells-> change date format....

[sharathnarayanan]

wow, where does this data come from? none of those entries in column M are dates, they are all text that looks like a date, and no formatting will change that.

Also in your expected results, although not a big deal, your 1st result is formatted as yyyy-mm-dd, and the rest is dd-mm-yyyy - which do you want?

OK to keep this simple, in M4, remove that leading space, otherwise the formula becomes more complex, and it looks like that is the only entry with a leading space.

Then use this to extract the date...
=DATE(MID(M4,7,4),MID(M4,4,2),MID(M4,1,2))
Format that as needed.

You will still be left with some errors, because some of your "dates" actually have the month name in them, instead of the month number. I suggest you use filters to ID those and manually correct them - or use Find/Replace

Sorry Fdibbins, my mistake…

I am working with a construction co. On a daily basis, I am getting this kind of work progress reports from different sources in different formats.. And I have to change the dates to this particular format before I upload in our Software.

Anyways, your formula helps me a lot and time saving... Thank you so much for your help....

[sharathnarayanan]

Hi,
first remove the space in the cell(Date). then select the date range right click -> format cells-> change date format....

Thank you..