Unix/Epoch Timestamp to DATETIME format?

jeddittmar

I'm trying to convert a Epoch Timestamp into a DATETIME format. I've had success using formulas getting it into a date, but am unable to get the time.

Anyone have any insight on how to accomplish this?

1517577336206 as a timestamp example which I'd like to convert to 2/2/2018 8:15:36

mcarlson

In Smartsheet, you can convert an Epoch timestamp into a DateTime format by separating the date and time components through a few formula steps. Here's how to break down the timestamp and convert it accurately.

Given your example timestamp 1517577336206, which is in milliseconds, follow these steps:

1. excelCopy code=1517577336206 / 1000
2. Calculate the Date and Time: Use Smartsheet’s date functions to convert the Epoch timestamp to a readable date and time format.
   • Smartsheet calculates dates from January 1, 1970. To convert your seconds-based timestamp, add the number of seconds since that epoch to that base date.
3. excelCopy code=DATE(1970, 1, 1) + (1517577336 / 86400)Here, 1517577336 is the timestamp in seconds (after dividing by 1000), and 86400 is the number of seconds in a day. This formula converts the timestamp to Smartsheet's date system.
4. If you cannot directly get the time in your cell format, extract the time separately by calculating the decimal portion of the day and formatting accordingly.

Final Output:

For full DateTime display, you might need to export this data to Excel or another tool that fully supports DateTime formatting with Epoch timestamps if Smartsheet’s formatting remains restrictive. However, the formula above will give you the correct date component directly in Smartsheet.

Please reach out with any additional questions!

mcarlson

In Smartsheet, you can convert an Epoch timestamp into a DateTime format by separating the date and time components through a few formula steps. Here's how to break down the timestamp and convert it accurately.

Given your example timestamp 1517577336206, which is in milliseconds, follow these steps:

1. excelCopy code=1517577336206 / 1000
2. Calculate the Date and Time: Use Smartsheet’s date functions to convert the Epoch timestamp to a readable date and time format.
   • Smartsheet calculates dates from January 1, 1970. To convert your seconds-based timestamp, add the number of seconds since that epoch to that base date.
3. excelCopy code=DATE(1970, 1, 1) + (1517577336 / 86400)Here, 1517577336 is the timestamp in seconds (after dividing by 1000), and 86400 is the number of seconds in a day. This formula converts the timestamp to Smartsheet's date system.
4. If you cannot directly get the time in your cell format, extract the time separately by calculating the decimal portion of the day and formatting accordingly.

Final Output:

For full DateTime display, you might need to export this data to Excel or another tool that fully supports DateTime formatting with Epoch timestamps if Smartsheet’s formatting remains restrictive. However, the formula above will give you the correct date component directly in Smartsheet.

Please reach out with any additional questions!