Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 05, 2018 08:40 AM
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:
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)
Jan 08, 2018 08:22 AM
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.
Jan 08, 2018 09:08 AM
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).
Jan 08, 2018 12:21 PM
Please take a look at this demonstration base.
{Date}
converted to a Unix timestamp using DATETIME_FORMAT()
.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!
Jan 08, 2018 12:48 PM
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!