Forum Discussion

Orstenpowers's avatar
Orstenpowers
Icon for Post Patron rankPost Patron
6 years ago
Solved

Text date into real date???

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...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
Orstenpowers
Icon for Post Patron rankPost Patron
6 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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