Converting UTC timestamp to Hawaii Standard Time timestamp
I've been importing data from Toggl (toggl.com) using Zapier (zapier.com) to SmartSheet. The date/times are recorded in Toggl in UTC, and I'm trying to convert this to HST (i.e. -10 hrs). My settings are for HST.
The UTC format: 2019-03-15T02:43:21+00:00
I've tried several suggestions in other posts/solutions for converting to a date - the best appears to be: =DATE(VALUE(LEFT([StartTimestamp]@row, 4)), VALUE(MID([StartTimestamp]@row, 6, 2)), VALUE(MID(StartTimestamp]@row, 9, 2)))
I have 2 problems:
1) The date I get with the formula only works if the 10 hr difference doesn't change the date e.g. from my sample UTC timestamp = 3/15/2019...when it actually was still 3/14/2019 in Hawaii.
2) The lack of TIMESTAMP functions within SmartSheet means that those are not an option in correcting for problem #1.
Any suggestions for a workaround?
Thanks
Andrea
p.s. I'll be adding my vote for adding TIMESTAMP functions as well
Comments
-
Hi Andrea,
Is the date/time that is being recorded specific to items (cells) in the sheet?
Or, is that date/time of when the data was moved into Smartsheet, and you would like that converted?
Thanks,
Kara
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!