Text date into real date???

[Orstenpowers]

Good Morning everybody,

The below, highlighted date column is formatted as text. When trying to change the format into date, I got the error message that this cannot be done automatically!?

What do I need to do? Hopefully someone out there that can help me!? Looking forward to your ideas I remain

I guess it is an easy one, but I don't get it.

[mwegener]

Hi @Orstenpowers ,

i would also prefer the power query away from @Ashish_Mathur .

If there are value errors in the column, they can be easily identified.

[Ashish_Mathur]

Hi,

In the Query Editor, selecting date works just fine for me.  See these screenshots

[v-frfei-msft · Accepted solution]

Hi @Orstenpowers ,

Here we go:

year = var year = RIGHT('Table'[Order Date],4)
var month =MID('Table'[Order Date],4,2)
var day =LEFT('Table'[Order Date],2)
return
date(year,month,day)

Pbix as attached.

[amitchandak]

Create a new date column like

New order date = mid(Table[Order Date],4,2) &"/"& left(Table[Order Date],2) & "/" & right(Table[Order Date],4)

[Orstenpowers]

@amitchandak  Thank you!
However, it did not work. The result was a text formatted column and when I tried to change this to "Date" format, the whole column stated "error"!? 

[amitchandak]

@Orstenpowers 

Check the first three column giving correct values, if note share date for which it is not doing. If yes, then try the fourth one

year =right(Table[Order Date],4)
month =mid(Table[Order Date],4,2)
day =left(Table[Order Date],2)
new date =date(right(Table[Order Date],4),mid(Table[Order Date],4,2) ,left(Table[Order Date],2))