Hours by timezone
Hi there, I have two things I need help on:
1: Getting duration to show "3 hours" or "60 minutes":
 Start time (ex. 9:00 AM)
 End time (ex. 12:00 PM)
 Duration (ex. 3 hours)
I used this formula to get a numeric number, but would love it to be "3 hours" or "60 minutes", etc.
=(IF(FIND(":", [End Time]@row) = 2, VALUE(LEFT([End Time]@row, 1)) + IF(CONTAINS("PM", [End Time]@row), 12, 0), IF(FIND(":", [End Time]@row) = 3, VALUE(LEFT([End Time]@row, 2)) + IF(AND(CONTAINS("12:", [End Time]@row), CONTAINS("PM", [End Time]@row)), 0, IF(CONTAINS("PM", [End Time]@row), 12, 0)))) + IFERROR(IF(VALUE(MID([End Time]@row, 3, 2)) > 0, VALUE(MID([End Time]@row, 3, 2)) / 60), IFERROR(IF(VALUE(MID([End Time]@row, 4, 2)) > 0, VALUE(MID([End Time]@row, 4, 2)) / 60), 0)))  (IF(FIND(":", [Start Time]@row) = 2, VALUE(LEFT([Start Time]@row, 1)) + IF(CONTAINS("PM", [Start Time]@row), 12, 0), IF(FIND(":", [Start Time]@row) = 3, VALUE(LEFT([Start Time]@row, 2)) + IF(AND(CONTAINS("12:", [Start Time]@row), CONTAINS("PM", [Start Time]@row)), 0, IF(CONTAINS("PM", [Start Time]@row), 12, 0)))) + IFERROR(IF(VALUE(MID([Start Time]@row, 3, 2)) > 0, VALUE(MID([Start Time]@row, 3, 2)) / 60), IFERROR(IF(VALUE(MID([Start Time]@row, 4, 2)) > 0, VALUE(MID([Start Time]@row, 4, 2)) / 60), 0)))
2: Having additional columns for the "start time" in different "timezones".
I'd like to just show the start time based on timezones. So if the event starts at 9:00 AM Pacific, it would add 2 hours to show a Mountain time of 11:00 AM and then for GMT it would show 17:00 (instead of a 12 hour, it would show 24 hours)
 Mountain Timezone (ex 11:00 AM)
 GMT (ex. 17:00 PM)
Answers

You should be able to find something in the below thread. You may need to dig as it is currently 11 pages, but I am fairly certain there are solutions in there for converting the number back into text output as well as time zones.

Paul's "Formulas for Calculating Time" are a great starting point for your project and the best single resource I know of.
I also strongly suggest submitting feature requests to Smartsheet for time related items. So many time related features should be built in to Smartsheet as Column data types and time formulas.

@Lee Joramo I believe there is already a time based enhancement request out there that you should be able to vote up. As much as I put into the time based solutions... I can honestly say that I wouldn't mind them becoming obsolete because of some increased capabilities.
Help Article Resources
Categories
Check out the Formula Handbook template!