Upload date and time field

[Jenn_Pellerin]

Hello -

I have a spreadsheet of people who were scanned at an event. It has the date and also time of when that occurred. I would like this to show in my datetime field I created for consenting to processing. My dumb question - how do I upload that? Would it be one column in the sheet and can Marketo on it's own see the difference between the date and time?

[SanfordWhiteman]

One column, absolutely. Use ISO 8601 DateTime, format "yyyy-MM-ddTHH:mm:ssZ" --

     2018-07-01T13:30:00Z

"Z" == "Zulu" == UTC time zone in this case.

[Jenn_Pellerin]

Thank you for the reply Sanford Whiteman​! These were scanned in a different country (I'm in California). So my understanding is if these were scanned at local time in London - let's say 4pm - then technically it would be uploaded as 3pm?

I appreciate your help

[SanfordWhiteman]

You should convert the times to UTC before importing for the best portability.

[Stephanie_Chho1]

Hi Jenn

Adding more to Sanford's comment -

This is the format you will need to use  this format: YYYY-MM-DDTHH:MM:SSGMT+00:00

For example if the Marketo instance time is Australia/Sydney which is GMT+10, if you uploaded the following 2017-07-10T01:00:00GMT+00:00, it will be uploaded to Marketo as 10 July 2017 11:00 AM.

I suggest to upload a small batch before uploading the rest of your data confirming the data.

[SanfordWhiteman]

But "GMT+00:00" isn't a valid ISO 8601 time zone offset.

The standard tz/offset syntax is just

     ±HH:MM

You can use

     +00:00

if you wish, but that's the same as

     Z

The best practice, as I mentioned, is to convert the times to UTC in the spreadsheet. That way the spreadsheet has the same meaning regardless of when and where it's imported. Datetimes should always be stored in UTC and only converted at display time.

[Jenn_Pellerin]

Thank you both for your help. Long story short, Marketo is still adding to additional hours to my timezone. No idea why but at least got the correct time down after finding that out.

[SanfordWhiteman]

When you include the Z? That's not right.