Date formatting - mix of slash and dash dates

[Averheim]

Since changing my laptop, all excel exports from our OM-system looks horrible in regards to the date format. The big issue is that all regions enter their dates differently in our OM system. I have no idea what my old Excel installation was set up to make it work, I simply can't get my head around how to format all dates at once without changing all slashes to dashes. Even if I would change the Slashes to Dashes I still have the problem of the order of the dates as "slash and dash-dates" has a different ordering. It would be nice if the formatting could be done in Excel and not by tempering with the date settings of my PC... An example file is attached. 

[#2]

@Averheim 

(i'm using a non-English system . Guess what i mean if my translation guesses are totally wrong...)

a formula solution might be:

=DATEVALUE(B2)

But that only works if your system (windows) is set to US-dates

Another way that usually works: 

Select column B

Data->Text to Columns

[Next]->[Next]

Now you should be able to select DATE and the format MonthDayYear MDY  (in-data format. Not what you want).

[End]

If you are lucky the Text-dates (/) are transformed to real dates and the "real dates " are untouched.

Chang the display-format If necessary (Ctrl+shift+3  Or right click. Format cells, Nubers format. Date)

The problem:

Some of your dates are "real" excel dates (-). And some are text (/)

select column, Right click, Format cells, alignment, Horizontal=General

Now you will se that the text is aligned to the Left and the "real" Dates to the right. 

Or change display-format of the column t general (Ctrl+0). The text-dates will still be text but the real, calculable dates will show upp as days since 1900-01-01  (44161)

You can use a formula that checks if its a date (number) or a text and only fix the text-Dates

=IF(ISNUMBER(B2),B2,DATE(MID(B2,7,4),MID(B2,1,2),MID(B2,4,2)))

But i think that should give the same result as method 1

else. Use power query with "Locale": 

https://support.microsoft.com/en-us/office/internationalization-power-query-d42b9390-1fff-413f-8120-...

Expand: Use a non-default locale setting on a Change Type operation

[#3]

@Averheim 

Text To Columns affects only texts, if dates are inside they won't be changed.