Format for Importing a DateTime field value from CSV

[Anonymous]

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

[Anonymous]

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

[Josh_Hill13]

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.

[Anonymous]

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).

  

- UTC and GMT format are not supported.

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

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

[Jonathan_Marzin]

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.

[Anonymous]

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.

[Anonymous]

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

It's a great change.

[Kenny_Elkington]

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.

[Jonathan_Marzin]

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".