Change text to date

[Anonymous]

Hello Everyone

I got a problem changing my text column into a date colum, i got the date like this 2018-01-10 (yy-mm-dd) but when I use this formula: 

real date =
IF (
    PP_ORDENESPRODUCCION[FECHA_ENTREGA_REAL] = "0000-00-00";
    BLANK ();
    DATE ( LEFT ( PP_ORDENESPRODUCCION[FECHA_ENTREGA_REAL]; 4 ); RIGHT ( LEFT ( PP_ORDENESPRODUCCION[FECHA_ENTREGA_REAL]; 4 ); 2 ); RIGHT ( PP_ORDENESPRODUCCION[FECHA_ENTREGA_REAL]; 2 ) )
)

my result is wrong: like this 10/6/2019 0:00:00, yes 2019 not 2018!

Does anyone Know what is happening?

Also the day is wrong

Thanks!

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

There existing a mistake when using above formula to extract Month value from text. 

Please modify it to:

real date =
IF (
    PP_ORDENESPRODUCCION[FECHA_ENTREGA_REAL] = "0000-00-00";
    BLANK ();
    DATE ( LEFT ( PP_ORDENESPRODUCCION[FECHA_ENTREGA_REAL]; 4 ); RIGHT ( LEFT ( PP_ORDENESPRODUCCION[FECHA_ENTREGA_REAL]; 7 ); 2 ); RIGHT ( PP_ORDENESPRODUCCION[FECHA_ENTREGA_REAL]; 2 ) )
)

Best regards,

Yuliana Gu

[AlB]

Hi @Anonymous

Does it not work by changing the date type of the column from text to date?