How to change YYYYMMDDHHHH into date format

[bouyazbekj]

Hello everyone, 

How do I go about changing a YYYYMMDDHHHH into a yyyy-mm-dd format. 

Ticket date

201605010000

201702280000

201605080000

201702260000

[AlexisOlson · Accepted solution]

The MID function can help simplify things a bit

DATEVALUE(
    LEFT(Table1[Ticket date],   4) &"-"&
    MID(Table1[Ticket date], 5, 2) &"-"&
    MID(Table1[Ticket date], 7, 2)
)

[bouyazbekj]

I keep getting this error "Cannot convert value '--' of type Text to type Date." 

[AlexisOlson]

That sounds like you've got some blanks date strings it's trying to interpret.

[AlB]

Hi @bouyazbekj

One option below. I am not sure if there are built-in functions in DAX that do this. There might.

Calculated column (setting seconds to 0)

Table1[Date] =
VAR VYEAR =
    LEFT ( Table1[Ticket date]; 4 )
VAR VMonth =
    RIGHT ( LEFT ( Table1[Ticket date]; 6 ); 2 )
VAR VDay =
    RIGHT ( LEFT ( Table1[Ticket date]; 8 ); 2 )
VAR VHour =
    LEFT ( RIGHT ( Table1[Ticket date]; 4 ); 2 )
VAR VMins =
    RIGHT ( Table1[Ticket date]; 2 )
RETURN
    DATE ( VYear; VMonth; VDay ) + TIME ( VHour; VMins; 0 )