Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

How to convert text 20150714 to date?

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's avatar
    v-qiuyu-msft
    9 years ago

    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

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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))))