Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Text won't turn to Date

Hi,    I have tried converting my text to date by changing the Data Type formatting in the Column tools but just receive an error. See pic below.  I also tried to create a column with the followin...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I figured it out. I had to create an If statement to get rid of the 0s. Then I created another column with the following equation: 

     

    Column 2 = Date(LEFT(BOOJ30MF[Requested date],4),MID(BOOJ30MF[Requested date],5,2) ,RIGHT(BOOJ30MF[Requested date],2))
     
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous 

Use

FORMAT(table[col],"MMDDYYYY")

Anonymous's avatar
Anonymous
Not applicable
6 years ago

@ pranit828 - This still gives me an error 😕

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    HI Anonymous .

     

    Try this formula:

     

    Date = Date(LEFT(BOOJ30MF[BOOJRSDT],4),LEFT(BOOJ30MF[MMDD],2) ,RIGHT(BOOJ30MF[MMDD],2))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi MFelix , 

       

      I am receiving the error "An argument of the function 'DATE' has the wrong data type or the result is too large or too small."

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        Hi Anonymous ,

         

        I have test it out and everything is working correcty can you confirm the formula you are using please.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

     

    What is the data type of [date] column?

     

    you can use the below function instead.

     

    DATE(RIGHT(BOOJ30MF[Date],4),LEFT(BOOJ30MF[Date],2),MID(BOOJ30MF[Date],3,2))