Date exctracting from specific timestamp

[Anonymous]

Hi community, 

Would you please give me advice what is the best way to extract the date from this specific timespamp:

P2019Y3M11DT11H4M54S

I've tried to use MID function to exctract the parts and concatenate them by ".": 

Date =

MID ( Test_table[orderDate] ; SEARCH ( "M" ; Test_table[orderDate] ) + 1 ; SEARCH ( "D" ; Test_table[orderDate] ) - SEARCH ( "M" ; Test_table[orderDate] ) - 1 ) & "." &

MID ( Test_table[orderDate] ; SEARCH ( "Y" ; Test_table[orderDate] ) + 1 ; SEARCH ( "M" ; Test_table[orderDate] ) - SEARCH ( "Y" ; Test_table[orderDate] ) - 1 ) & "." &

MID ( Test_table[orderDate] ; SEARCH ( "P" ; Test_table[orderDate] ) + 1 ; 4 )

As result I received a text string "11.3.2019", but not able to change the format to Date.

Would be really appreciate for your help.

Thank you!

[Anonymous]

Thank you guys, now it works!

[TomMartens · Accepted solution]

Hey,

I would do something like this:

DATE(
    VALUE(< something that returns the year >)
    ,VALUE(< something that returns the month >)
    ,VALUE(< something that returns the day >)
)

Regards,
Tom

[AliH]

Hi Egor,

test:=date(mid("P2019Y3M11DT11H4M54S",find("P","P2019Y3M11DT11H4M54S")+1,4),mid("P2019Y3M11DT11H4M54S",find("M","P2019Y3M11DT11H4M54S")+1,2),mid("P2019Y3M11DT11H4M54S",find("Y","P2019Y3M11DT11H4M54S")+1,1))

Probably not the best way, but this works.