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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        az38

        Yes , I've checked that I copied correctly.