How to calculate UTC given the local time and the UTC offset please

AJ Cruz

Greetings
I'm working on a sheet in which I'll need to calculate UTC from a given local time and UTC offset and populate into the Planned Migration Time column.

Here are my columns:

I will be using the output from that to calculate the other columns but want to focus only on calculating UTC for this exercise. Below is my current formula but challenged with consistent results

=IF(NOT(ISBLANK([Approved Planned Migration Time (24HR Clock)]@row)), IF([UTC Offset]@row < 0, IF(VALUE(LEFT([Approved Planned Migration Time (24HR Clock)]@row, 2)) + ABS([UTC Offset]@row) > 24, TIME(VALUE(LEFT([Approved Planned Migration Time (24HR Clock)]@row, 2)) + ABS([UTC Offset]@row) - 24 + ":" + RIGHT([Approved Planned Migration Time (24HR Clock)]@row, 2), 1, 0), TIME(VALUE(LEFT([Approved Planned Migration Time (24HR Clock)]@row, 2)) + ABS([UTC Offset]@row) + ":" + RIGHT([Approved Planned Migration Time (24HR Clock)]@row, 2), 1, 0)), IF(VALUE(LEFT([Avery Approved Planned Migration Time (24HR Clock)]@row, 2)) - ABS([UTC Offset]@row) < 0, TIME(24 - (VALUE(LEFT([Approved Planned Migration Time (24HR Clock)]@row, 2)) + ABS([UTC Offset]@row)) + ":" + RIGHT([Approved Planned Migration Time (24HR Clock)]@row, 2), 1, 0), TIME(VALUE(LEFT([Approved Planned Migration Time (24HR Clock)]@row, 2)) - ABS([UTC Offset]@row) + ":" + RIGHT([Approved Planned Migration Time (24HR Clock)]@row, 2), 1, 0))), "")

What would be a good formula or modification to the one provided to help with calculating UTC time from local given a certain offset?

Brian_Richardson

Check out this excellent stepthrough of a similar solution by @Xochitl C. You should be able to adjust the offsets used in these formulas for UTC instead of CST

https://community.smartsheet.com/discussion/118430/help-to-set-a-time-column-to-cst-time-zone

AJ Cruz

Thank you Brian
From the response received, here are some observations based on the solution suggested in the post

1. This solution hard codes for a single UTC offset. How would I calculate from UTC over regions with different offsets and .5 offset (such as countries like India)?
2. This formula does not appear to cope with either the 1st of a month and going backwards or the end of the month and going forwards. How would this be solved?

Updated formula I'm working with given the additional insight

=IF(NOT(ISBLANK([Local Date]@row)), IF([UTC Offset]@row < 0, IF(VALUE(LEFT([Local Time]@row, 2))+ ABS([UTC Offset]@row) > 24, TIME(VALUE(LEFT([Local Time)]@row, 2))+ ABS([UTC Offset]@row) - 24 + ":" + RIGHT([Local Time]@row, 2), 1, 0), TIME(VALUE(LEFT([Local Time]@row, 2))+ ABS([UTC Offset]@row) + ":" + RIGHT([Local time]@row, 2), 1, 0)), IF(VALUE(LEFT([Local time]@row, 2))- ABS([UTC Offset]@row) < 0, TIME(24 - (VALUE(LEFT([Local time]@row, 2))+ ABS([UTC Offset]@row))+ ":" + RIGHT([Local Time]@row, 2), 1, 0), TIME(VALUE(LEFT([Local time]@row, 2))- ABS([UTC Offset]@row) + ":" + RIGHT([Local time]@row, 2), 1, 0))), "")

Thanks!

Brian_Richardson

AJ do you have Bridge? There’s a simple workflow that can figure conversions like this for time zones and handle all the various conditions with ease.

AJ Cruz

Good day Brian
I am not familiar with Bridge. I'm assuming it would be similar to Timeanddate.com as an online timezone convertor. I was hoping to be able to do it in a dynamic kind of way through SmartSheet. I'm hoping that with the right syntax, I would be able to work with it

Brian_Richardson

Hey AJ, Bridge is a Smartsheet addon that allows you to run advanced automations and address the Smartsheet API, as well as integrate with other tools and run JavaScript.

As far as your formula, I don’t have my laptop right now, but I would leverage the TIME function to perform your calculations in combination with simple add and subtract 1 to your date. TIME will allow you to avoid having to use IF to figure out if you are reducing the hour less than 0 or more than 24 as it should return the correct hour value, rolling over from 23:99 to 00:00 for example.

Also, Smartsheet can add and subtract from dates (-1 meaning 1 day) and will return a new date incorporating a month transition if needed without you having to calculate it yourself.

You will still likely need to use IF to determine whether the hour rolls over and thereby introduce the need to add or subtract 1 from the date.

https://help.smartsheet.com/function/time