Forum Discussion

godtooro's avatar
godtooro
Helper II
3 years ago
Solved

Column DATE from column (20310312)

Dear all,   I've a column in this format: 20210312 (YYYYMMDD). I want to create a new colum date. How can I do it? Because with date(column) it doesn´t works.
Need Help
Tips and Tricks
  • Samarth_18's avatar
    Samarth_18
    3 years ago

    Hi godtooro ,

     

    You could try below code:-

    new_date = 
VAR _year =
    MID ( 'Table'[Column1], 1, 4 )
VAR _month =
    MID ( 'Table'[Column1], 5, 2 )
VAR _day =
    MID ( 'Table'[Column1], 7, 2 )
RETURN
 DATEVALUE ( _year & "-" & _month & "-" & _day )

    Output:-

     

    Thanks,

    Samarth

     

ichavarria's avatar
ichavarria
Solution Specialist
3 years ago

Hi godtooro,

 

You can use the "FORMAT" function to convert the date in the YYYYMMDD format to a date format that can be used for analysis. Here's an example formula:

 

Date = DATEVALUE(FORMAT(Table1[Column], "####/##/##"))

 

In this formula, "Table1" refers to the name of your table and "Column" refers to the name of the column containing the YYYYMMDD date format. The "FORMAT" function converts the date to the format "####/##/##", which is the format recognized by the "DATEVALUE" function.

 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly

  • godtooro's avatar
    godtooro
    Helper II
    3 years ago

    Thank you so much!

    I've this error: number entry "20210329" is out of the interval YYYY/MM/DD.

    Do you know why it is?