Date Formats switching between American and English in the same column

[Anonymous]

Has anyone else come across this problem or varying formats on dates in the same column?

I have a column of text, some with dates in, some with "TBC" and "N/A", so I've created a new column extracting into a date format anything with a LEN > 5.

As an example, the text "25/04/2018" extracts to this new column as "25 April 2018"

but "12/03/2018" extracts as "03 December 2018"

I've checked the Excel source formatting and there is no issue there so I'm a little stuck at the minute.

[Anonymous]

Hi @Anonymous 

I came across the exact same issue. Reformatting the data type to 'Date' via the Modeling tab didn't work for me.

However, going into 'Edit Queries' and transforming the data type here from 'Any' into 'Date' displayed the correct format across the whole column of dates.

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may use DATE Function to add a calculated column.

Column =
DATE ( RIGHT ( Table1[Column1], 4 ), MID ( Table1[Column1], 4, 2 ), LEFT ( Table1[Column1], 2 ) )