Skip to main content

Convert Excel Serial Dates to Date String

M

Problem Scenario:

  • Airtables datetime_parse function is unable to parse a date from a 1970 serial format.

  • A date column is pasted in your Table in as a serial number (ex UNIX format or 1970 serial format).
    (Lets say that column is called "InstrumentDate)

  • That date column needs to be in a human readable format.

  • People are constantly updating the Table, thereby preventing conversion/fixing by copying the column from excel to convert and repaste.

Example of Problem:

One date column contains date serial values, ex 1515430171 (ie the 1970 serial format of the date).
The date should instead should be reading something like 2018-01-08 .

Solution:

  • Create a new column in your table (I named mine “InstrumentDate_readable”)
  • make the column’s field type a formula, and paste the following formula into it:

DATETIME_FORMAT( DATEADD( ‘January 1,\n 1970’ , ( FLOOR( InstrumentDate - 25569 ) * 86400 ) , ‘seconds’) , ‘mm/dd/yyyy’ )

Change the italicized text to the name of the field that you are trying to convert.

Big thanks to SilkFire for posting his answer to similar problem on stackoverflow.com (see Converting Excel Date Serial Number to Date using Javascript)

    4 replies

    W
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • January 8, 2018

    Nice solution — but this functionality is built into Airtable.

    For 9- or 10-digit timestamps, use this formula:

    DATETIME_PARSE(InstrumentDate,'X')

    For 12-digit (Unix millesecond timestamps) use

    DATETIME_PARSE(InstrumentDate,'x')

    Airtable supports an embarrassment of possible date/time formats, but it’s easy to overlook the documentation page.

    M
    1 person likes this
    • M
      Matthew_Billio1
    M
    • Matthew_Billio1
    • Author
    • Known Participant
    • 41 replies
    • January 8, 2018
    W_Vann_Hall wrote:

    Nice solution — but this functionality is built into Airtable.

    For 9- or 10-digit timestamps, use this formula:

    DATETIME_PARSE(InstrumentDate,'X')

    For 12-digit (Unix millesecond timestamps) use

    DATETIME_PARSE(InstrumentDate,'x')

    Airtable supports an embarrassment of possible date/time formats, but it’s easy to overlook the documentation page.


    Hello @W_Vann_Hall,

    Thank you very much for taking the time to post, and for your willingness to post a native solution! My original post must have been unclear. I have updated it to emphasize and/or add following points:

    • Airtables datetime_parse function is unable to parse a date from a 1970 serial format.

    • 1970 format represents the number of miliseconds lapsed since January 0, 1970. It is the way most unix systems (and programs) “see” the current time. For example:

      1515430171 is the 1970 serial format for the current date/time (2018-01-08T16:49:31Z).

    • From what I can tell, Airtable’s dateTime_parse function appears to only convert from gregorian formats (what I call humanly readable formats).

    W
    • W_Vann_Hall
    • Inspiring
    • 1386 replies
    • January 8, 2018
    Matthew_Billio1 wrote:

    Hello @W_Vann_Hall,

    Thank you very much for taking the time to post, and for your willingness to post a native solution! My original post must have been unclear. I have updated it to emphasize and/or add following points:

    • Airtables datetime_parse function is unable to parse a date from a 1970 serial format.

    • 1970 format represents the number of miliseconds lapsed since January 0, 1970. It is the way most unix systems (and programs) “see” the current time. For example:

      1515430171 is the 1970 serial format for the current date/time (2018-01-08T16:49:31Z).

    • From what I can tell, Airtable’s dateTime_parse function appears to only convert from gregorian formats (what I call humanly readable formats).


    Please take a look at this demonstration base.

    • The left-most column consists of random dates stored in an Airtable Date field.
    • The second column is {Date} converted to a Unix timestamp using DATETIME_FORMAT().
    • The third column is the Unix timestamp rendered as a string by copying the values from column 2 and pasting them into a Single-line Text field.
    • The right-most column is that textual representation of the Unix timestamp converted into an Airtable Date using DATETIME_PARSE(). As you can see, Airtable can convert from the Unix timestamp to its standard Date format natively.

    Take a look at the page I linked to in light of the two formulas I provided — I think you’ll find DATETIME_PARSE() and DATETIME_FORMAT() to be more capable than you think!

    M
    1 person likes this
    • M
      Matthew_Billio1
    M
    • Matthew_Billio1
    • Author
    • Known Participant
    • 41 replies
    • January 8, 2018
    W_Vann_Hall wrote:

    Please take a look at this demonstration base.

    • The left-most column consists of random dates stored in an Airtable Date field.
    • The second column is {Date} converted to a Unix timestamp using DATETIME_FORMAT().
    • The third column is the Unix timestamp rendered as a string by copying the values from column 2 and pasting them into a Single-line Text field.
    • The right-most column is that textual representation of the Unix timestamp converted into an Airtable Date using DATETIME_PARSE(). As you can see, Airtable can convert from the Unix timestamp to its standard Date format natively.

    Take a look at the page I linked to in light of the two formulas I provided — I think you’ll find DATETIME_PARSE() and DATETIME_FORMAT() to be more capable than you think!


    Got it! Thanks a million for explaining that in detail!

    I presumed your “x” was a representation of “some variable”, but you really meant use “x”
    :grinning_face_with_sweat:

    thanks for you patience!

    Reply


    Most helpful members this week

    Terms & Conditions