How to calculate UTC given the local time and the UTC offset please
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?
Answers

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

Thank you Brian
From the response received, here are some observations based on the solution suggested in the post 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)?
 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!

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.

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 
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
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!