Converting UTC timestamp to Hawaii Standard Time timestamp

AndreaM

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

 

Kara Lumley

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