Hello,

I've been trying to figure out to change a text format on excel to DD-MM-YY format but no luck so far. I tried almost all the formulas that I know but didn't work out. Can someone please help me as I have a huge list of data that needs to be converted to DD-MM-YY format.

This is an example of my data: (I'm unable to change the format as there isn't any hyphen signs)
May 27 2011
Nov 30 2009

Desired format>>>
27-May-2011
30-Nov-2009

Can someone please help me with a formula asap.

Many Thanks in advance!

Cheryl

Don't use a formula. Go to Formatting and create a Custom date format then apply the format to the cells.

Actually, if the cells have the exact strings you posted above, then the real problem is they aren't really dates yet, they are text strings. First, let's use a trick to convert the text into real dates.

1) Highlight all the dates in a single column
2) Select Data > Text to Columns > Delimited > Next
3) Uncheck all the delimiters on page 2
4) Click Next > Column Data format > Date: MDY > Finish


On my system this immediately converted your text strings into the "desired format" you wanted, but if they don't on yours, then you can now use Scott's suggestion to apply a date format to those cells for the format you want.

Repeat with each column of dates, one column at a time.