Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date format from text

I have a column that's coming in as text that supposed to be a date. I want to convert that text to date but when I use Format(Date,"##/##/##") and then go to change it type it says format() is not a...
Need Help
  • az38's avatar
    az38
    6 years ago

    Anonymous 

    yes.

    try

    Column = 
IF(ISBLANK([DATE]), BLANK(),
DATE(VALUE(RIGHT([DATE], 2)) + 2000, VALUE(LEFT([DATE], LEN([DATE])-4)), VALUE(MID([DATE],LEN([DATE])-3,2)))
)
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

You can parse this out with DAX text functions, but this is a great application of Column from Examples in the power query editor.  Please see this link - https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-add-column-from-example

 

I used it on similar data and it automatically created a custom column with this M expression

Text.Combine({Text.Reverse(Text.Middle(Text.Reverse(Text.From([Date], "en-US")), 4)), "/", Text.Reverse(Text.Middle(Text.Reverse(Text.From([Date], "en-US")), 2, 2)), "/20", Text.End(Text.From([Date], "en-US")

 

If you still want to do it with DAX, here is one way to do it

NewDateColumn = Left('Date'[Date],LEN('Date'[Date])-4)&"/"&Left(Right('Date'[Date],4),2)&"/"&RIGHT('Date'[Date],2)

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks mahoneypat  for the insightful information! 

     

    When I tried to use the DAX statement I get this error.