Converting UTC timestamp to Hawaii Standard Time timestamp

edited 12/09/19 in Formulas and Functions

I've been importing data from Toggl ( using Zapier ( 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?



p.s. I'll be adding my vote for adding TIMESTAMP functions as well



  • 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?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!