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 able to convert text to date.

 

Example: 81015 = 8/10/15:

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

10 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Anonymous 

    create a calculated column

    Column = DATE(VALUE(RIGHT([DATE], 2)) + 2000, VALUE(LEFT([DATE], LEN([DATE])-4)), VALUE(MID([DATE],LEN([DATE])-3,2)))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      az38  Thanks for the information but I get "An argument of function 'LEFT' has the wrong data type or has an invalid value/" error.

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Anonymous 

        are you sure you copied the statement example as I suggested? it works as appropriated for me, I checked twice

  • mahoneypat's avatar
    mahoneypat
    Microsoft 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.