Format 2021-05-17T04:00:00.000Z to date

[tuan_nguyen]

Hi everyone,
How can i format  2021-05-17T04:00:00.000Z to datetime
I use TextBetween(SubField(Replace(' 2021-05-17T04:00:00.000Z ', '"', ''), 'start_at:', 2), '', ',') but not work

Thanks.

[rubenmarin · Accepted solution]

Hi, you can use Timestamp#() to specify the input timestamp format:

=Date(Timestamp#('2021-05-17T04:00:00.000Z','YYYY-MM-DDThh:mm:ss.tttZ'))

The previous internally stores the time, just it's not show, to remove the time part:

=Date(Floor(Timestamp#('2021-05-17T04:00:00.000Z','YYYY-MM-DDThh:mm:ss.tttZ')))

Or to keep and also show the time part:

=Timestamp(Timestamp#('2021-05-17T04:00:00.000Z','YYYY-MM-DDThh:mm:ss.tttZ'))

[Øystein_Kolsrud]

'T' is a marker for am/pm I believe, so you'll need to get rid of it from the string. Here's an example:

Timestamp#(
  Replace('2021-05-17T04:00:00.000Z','T',' '),
  'YYYY-MM-DD HH:mm:ss.fffZ'
)

[tuan_nguyen]

Hi @rubenmarin ,
I tried your way, if hard coded like that then the data show up as i want, but if i use variable like in the picture it gets error, can you help me

[rubenmarin]

Hi, can you upload a sample app with only that field loaded to make some tests?

Btw, I missed the key and the tttZ should be fffZ, but if you only want to keep the date it should not have any impact.

[tuan_nguyen]

I use this data in file csv, so i can't upload this for you, sr 😞

[rubenmarin]

I meant to make a new app, just copy the script that loads the datetime field and upload that qvf that only has reservation_start_at field.

This is to create a table like the one you posted before, so I can take a look on the field make some test to conver to date.

[tuan_nguyen]

i use qlik in the web, so i don't know how to make a new app, can i upload the file csv for you?

[rubenmarin]

Ok, do a copy of the csv and only keep the datetime field. Just because maybe there is some info that it's better to not upload to internet.

[tuan_nguyen]

Here is file csv.

Thanks for your help.