Formula to change date american (month first) to uk style (month second)

Hi,

I have a list of dates in the american format (month-day-year) and I want to change it to uk format (day-month-year) with a formula.

Please can someone help!

Attached the dates - I would appreciate if u could put it the formula into the excel and upload!

Thanks,

Leo

Although they look like dates in the US format, it appears that they have been entered as General text - for one thing they are left-aligned and dates by default are right-aligned. I selected all the data, opened the Format Cells dialog box (keyboard shortcut CTRL+1), under the Number tab, selected Date, then selected English (UK) ... and nothing happened BECAUSE they have been entered as text and not in Date format to start with. So you may have to create a formula in the B column to change them all to a date format.

This may not be the perfect solution, and I'm sure there are smarter people than me on this Forum who could do a better conversion, but have a look at this:

DATE CONVERT.xlsx

By the way, a smaller example would have been appreciated rather than hundreds of rows.

Does this do what you want?

=DATE(YEAR(A3),DAY(A3),MONTH(A3))
in B3 & drag it down

Punnam