# 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)

• 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!