Format Unix timestamp as human readable date

[Vernon_Fowler]

Using Buffer (through Zapier) I get a Unix timestamp for when each post is scheduled.
An example Unix timestamp is 1497503280.
The expected human readable equivalent is 15/6/2017 3:08pm (for Australian Eastern Standard Time which is GMT +10).

Using a formula based on Excel convert (via https://www.epochconverter.com/), the nearest I can get to seeing the output in a human readable form is by:

DATETIME_PARSE( ( ( {BufferUnixTimeStamp} +36000 ) / 86400 ) +25569 )

This doesn’t work as all it produces is the start of the epoch (1/1/1970 12:00am) no matter what the input value is for {BufferUnixTimeStamp}.

Hope someone can help with converting Unix timestamp to a human readable date time.

[Christoff]

DATETIME_PARSE() requires an input that is formatted like a date, e.g. “06/05/17” or “6 May 2017”.

You can use the DATEADD() function to convert the Unix timestamp.

For Australian Eastern Standard Time, use: DATEADD(“1/1/1970”,({BufferUnixTimeStamp}+36000),“s”).

[Vernon_Fowler]

Bingo! Thank you @Christoff That’s perfect! Yihaa!

[W_Vann_Hall]

Alternatively, you can use

DATETIME_PARSE({BufferUnixTimeStamp},'X','Australia/Sydney')

‘X’ says the input string is a 10-digit Unix timestamp; for the 13-digit Unix millisecond timestamp, use ‘x’. Unfortunately, you’re limited to ‘M/D/YYYY h:mm a’ as the output format…

[Vernon_Fowler]

Thanks @W_Vann_Hall That’s much neater! And we can format differently via the Formatting tab.

Voila! :thumbs_up:

[W_Vann_Hall]

Heh… That’ll teach me to take the Airtable Formula Field Reference at its word. :winking_face: