
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Format 2021-05-17T04:00:00.000Z to date
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.
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
'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'
)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I use this data in file csv, so i can't upload this for you, sr 😞

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here is file csv.
Thanks for your help.

- « Previous Replies
-
- 1
- 2
- Next Replies »