Skip to main content
Jenn_Pellerin
Jenn_Pellerin
Level 2
June 13, 2018
Question

Upload date and time field

  • June 13, 2018
  • 3 replies
  • 5397 views

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?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

3 replies

SanfordWhiteman
SanfordWhiteman
Level 10
June 14, 2018

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
Jenn_PellerinAuthor
Level 2
June 14, 2018

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
SanfordWhiteman
Level 10
June 14, 2018

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

S
Stephanie_Chho1
Level 2
June 14, 2018

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.

Kind regardsStephanie
SanfordWhiteman
SanfordWhiteman
Level 10
June 14, 2018

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
Jenn_PellerinAuthor
Level 2
June 22, 2018

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
SanfordWhiteman
Level 10
June 22, 2018

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

Terms & ConditionsAccessibility statement

Loading footer...