Hours by timezone

Chelcie Hill

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)

Paul Newcome

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.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Lee Joramo

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.

Paul Newcome

@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.