Dynamic Date - Converting Date Format from US to UK

[Zakky]

Hi All, Is there a way to make the formula dynamic so it works on all different formats (only works on the first date at the moment). Any help would be greatly appreciated. Thank you.


)

 

[Cubist]

@Alex Blakenburg
Good catch. I suspected all cells in A are texts and valid dates did not registered as dates.

 

[Dave Patton]

Thanks for the feedback.
Use cubist's suggestion.

I incorrectly assumed that there was a problem with 2 digits for years.

 

[Micron]

Micron, How do you format them to UK dates?

My go-to shortcut is Ctrl+1 when I've selected a cell or range. That raises the format dialog box. On the left side I choose Date data type (I guess that's ok if your data is date data type) and pick UK format from the right list. If your data is text data type then maybe that won't work.

 

[Zakky]

Micron, I see what you mean. You learn something new every day. Good to know. Thank you. Have a good day.

 

[Cubist]

I guess I just don't get it. I copied the US dates to the next column and formated them to UK dates and got the posted result. Why the need for complex formulas?

You can only format actual dates or numerical data types. Since the OP's regional date format is dd/mm/yyyy, the mm/dd/yyyy format does not register as date type but text instead. Hence the need for a formula to swap the months and days.