How to convert text 20150714 to date?

[Anonymous]

I have Excel as data source and date is coming as 20150714. How to parse or convert to date format?

And how to make month slicer?

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

As the text value "20150714" is not formatted as standard date format, it's not able to convert it to Date data type directly.

In your scenario, you can try the DAX shared by @joerykeizer,

Also you can create a Custom Column in Query Editor follow below formula:

=Date.FromText(Text.Range([Column1],0,4)&Text.Range([Column1],4,2)&Text.Range([Column1],6,2))

Best Regards,
Qiuyun Yu

[Anonymous]

Text To Date

=Date.FromText(Text.Range([Column1],0,4)&Text.Range([Column1],4,2)&Text.Range([Column1],6,2))

Number to Date

=Date.FromText(Text.Range(Number.ToText([Column1]),0,4) & (Text.Range(Number.ToText([Column1]),4,2)& (Text.Range(Number.ToText([Column1]),6,2))))

[BhaveshPatel]

Just select the data type to "Date" by right clicking the column in Query ediotr to convert it into date column. 

For a month Slicer, You can drag and drop the date field to the slicer visual and select only month.

[Anonymous]

Modeling => Data Type: Date = > "We can't automatically convert this column to date type"

[joerykeizer]

Not sure if it's the best way, but you could create a new column like this:

Date = DATE(LEFT([DateText];4);MID([DateTest];5;2);RIGHT([DateText];2))

You specify which part of the text is year, month and day.

Btw you could also do this in the Query Editor by creating a new column:

=Date.FromText([DateText],"en-Us")