Format for Importing a DateTime field value from CSV | Community
Skip to main content
A
Anonymous
November 28, 2014
Question

Format for Importing a DateTime field value from CSV

  • November 28, 2014
  • 4 replies
  • 7832 views
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
    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    4 replies

    A
    Anonymous
    November 28, 2014
    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
      Josh_Hill13
      Level 10
      November 29, 2014
      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.
      A
      Anonymous
      June 4, 2015

      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
        Jonathan_Marzin
        Level 2
        October 19, 2015

        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.

        A
        Anonymous
        October 20, 2015

        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.

        Kenny_Elkington
        Adobe Employee
        Kenny_ElkingtonAdobe Employee
        Adobe Employee
        October 19, 2015

        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
        Jonathan_Marzin
        Level 2
        October 19, 2015

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

        Terms & ConditionsAccessibility statement

        Loading footer...