Format for Importing a DateTime field value from CSV

Highlighted
Anonymous
Not applicable

Format for Importing a DateTime field value from CSV

I've imported a csv file containing DateTime field formatted as YYYY-MM-DD hh:mm am/pm however it seems that the Marketo server is trying to auto-adjust the time on import to match the Account locale / timezone (EST Sydney) but messing it up in the process. I can get it to import the correct time if I manually update all the times in the spreadsheet (a timeconsuming process) to add +9 hours to the time, but then this throws out the date on import (e.g. if I want to import 4th August 2014 at 13:33, I have to format it as 2014-08-04 22:33 PM to get the time to import as 1:33 PM but then it throws the date out to 5th August in the process). 

This is killing my soul. All suggestions are welcomed 🙂

Carrie
Tags (1)
8 REPLIES 8
Highlighted
Anonymous
Not applicable

Re: Format for Importing a DateTime field value from CSV

Hi Carrie,

Could this be becuase your Marketo account is set to EST Sydney time?  Maybe if you chaged this (Admin > My Account > Time Zone)?

Simon
Highlighted
Level 10 - Champion Alumni

Re: Format for Importing a DateTime field value from CSV

Can you import it as a text field instead of using the Date field?

Check the existing format of {{system.dateTime}} to match it how Marketo expects a date field (assuming your field is a date field in Marketo).

As for the time zone, that's a bit odd. Perhaps you could do what Simon suggests and temporarily change the system time zone...but that might affect existing triggers and behavior data during that time of the change.
Highlighted
Anonymous
Not applicable

Re: Format for Importing a DateTime field value from CSV

I had a same question today and I found some CSV rules.

Marketo CSV datetime rules.

- CSV datetime format has to be yyyy-mm-dd hh:mm:ss or yyyy-mm-dd hh:mm

  if you use date separater '/', Date has to be m/d/yyyy order. See Import Members from a Spreadsheet into a Program - Marketo Docs - Product Docs

  If it is not m/d/yyyy order, Datetime will not be correct.  eg. 2015/6/1 => Jan. 6th 2015

- CSV datetime has to be UTC-5

   We have to create a new column and calculate UTC-5.

   For example, when I'm working in Japan(UTC+9). I have to create a column(-14hour).

   Marketo_Import_Datetime.jpg

- UTC and GMT format are not supported.

  12:34:56+9:00           =>NG

  12:34:56 GMT+9:00  =>NG

Highlighted

Re: Format for Importing a DateTime field value from CSV

Will it always be UTC-5 even in daylight savings?

UTC-5 is EST during standard time, but CT during daylight savings. I just wonder if they expect EST instead, which would be UTC-5 or UTC-4 depending on whether daylight savings in the USA.

Highlighted
Anonymous
Not applicable

Re: Format for Importing a DateTime field value from CSV

Yes, always.

It seems marketo internal clock is utc-5 always.

Almost marketo UI is supported user timezone.

But if you use api's, i recommend you remember this rule.

Highlighted
Anonymous
Not applicable

Re: Format for Importing a DateTime field value from CSV

Now W3C Date and Time Formats​ is supported as Kenny Elkington said

It's a great change.

Highlighted
Marketo Employee

Re: Format for Importing a DateTime field value from CSV

You should use W3C formatted datetimes whenever possible:  YYYY-mm-ddThh:mm:ssz for UTC or YYY-m-dThh:mm:ss-00:00 with an offset.

Highlighted

Re: Format for Importing a DateTime field value from CSV

This is what I do for API calls, but the W3C formats don't work for CSV imports. If I send a datetime like "2015-11-11T16:07:38+00:00" within a csv, it gets set to "2015-11-11 00:00:00".