Unix/Epoch Timestamp to DATETIME format?
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
Best Answer
-
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:- excelCopy code=1517577336206 / 1000
- 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.
- excelCopy code=DATE(1970, 1, 1) + (1517577336 / 86400)Here,
1517577336
is the timestamp in seconds (after dividing by 1000), and86400
is the number of seconds in a day. This formula converts the timestamp to Smartsheet's date system. - 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!
Murphy Carlson
DigitalRadius, Smartsheet Platinum Partner
mcarlson@digitalradius.com
Answers
-
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:- excelCopy code=1517577336206 / 1000
- 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.
- excelCopy code=DATE(1970, 1, 1) + (1517577336 / 86400)Here,
1517577336
is the timestamp in seconds (after dividing by 1000), and86400
is the number of seconds in a day. This formula converts the timestamp to Smartsheet's date system. - 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!
Murphy Carlson
DigitalRadius, Smartsheet Platinum Partner
mcarlson@digitalradius.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!