Calculating time
Hello All,
I'm a new user to Smartsheet and am trying to figure out the formula for calculating time. I have a start time column and an end time column and am trying to figure out the formula to calculate the amount of time in minutes. Any help would be appreciated!
Best Answer

Smartsheet is not set up for calculation time durations the way it is dates. However, I think I figured out a way to do this even listing times as you have here.
First, you need to create two "helper" text columns that will help isolate the hour markers and convert them from text to recognizable values (can be hidden once set up). Additionally, it will calculate how far in the hour your minutes by adding it as a decimal.
In the "Start Time Hour Helper" column, I have the following column formula:
=IF(LEN([Start Time]@row) = 7, VALUE(LEFT([Start Time]@row, 1)) + (VALUE(MID([Start Time]@row, 3, 2)) / 60), IF(LEN([Start Time]@row) = 8, VALUE(LEFT([Start Time]@row, 2)) + (VALUE(MID([Start Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))
In the "End Time Hour Helper" column, I have the following column formula:
=IF(LEN([End Time]@row) = 7, VALUE(LEFT([End Time]@row, 1)) + (VALUE(MID([End Time]@row, 3, 2)) / 60), IF(LEN([End Time]@row) = 8, VALUE(LEFT([End Time]@row, 2)) + (VALUE(MID([End Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))
Now, in your "Training Duration (min)" column, you need to be able to show the difference in hours multiplied by 60 minutes, while also accounting for the chance that hours may cross over from AM to PM since this example does not use military time. For this column, I wrote the following formula:
=IF(OR(AND(CONTAINS("AM", [Start Time]@row), CONTAINS("AM", [End Time]@row)), AND(CONTAINS("PM", [Start Time]@row), CONTAINS("PM", [End Time]@row))), ([End Time Hour Helper]@row  [Start Time Hour Helper]@row) * 60, IF(VALUE(LEFT([End Time]@row, 2)) = 12, ([End Time Hour Helper]@row  [Start Time Hour Helper]@row) * 60, (([End Time Hour Helper]@row + 12)  [Start Time Hour Helper]@row) * 60))
Assumptions:
Sessions are in the same day (none that cross the overnight threshold)
You will write start and end times one of two ways (with either 7 or 8 characters, including spaces). To ensure accuracy, I would add all the hours in the day as drop down values in your Start and End Time columns formatted the same way, assuming sessions start at predictable intervals (i.e. every half hour, 15 minutes, hour, etc.)
Here's a screenshot of what the finished product of these formulas looks like on my screen:
In any case, this was a fun one to figure out. Hope this helps!:)
Answers

Smartsheet is not set up for calculation time durations the way it is dates. However, I think I figured out a way to do this even listing times as you have here.
First, you need to create two "helper" text columns that will help isolate the hour markers and convert them from text to recognizable values (can be hidden once set up). Additionally, it will calculate how far in the hour your minutes by adding it as a decimal.
In the "Start Time Hour Helper" column, I have the following column formula:
=IF(LEN([Start Time]@row) = 7, VALUE(LEFT([Start Time]@row, 1)) + (VALUE(MID([Start Time]@row, 3, 2)) / 60), IF(LEN([Start Time]@row) = 8, VALUE(LEFT([Start Time]@row, 2)) + (VALUE(MID([Start Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))
In the "End Time Hour Helper" column, I have the following column formula:
=IF(LEN([End Time]@row) = 7, VALUE(LEFT([End Time]@row, 1)) + (VALUE(MID([End Time]@row, 3, 2)) / 60), IF(LEN([End Time]@row) = 8, VALUE(LEFT([End Time]@row, 2)) + (VALUE(MID([End Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))
Now, in your "Training Duration (min)" column, you need to be able to show the difference in hours multiplied by 60 minutes, while also accounting for the chance that hours may cross over from AM to PM since this example does not use military time. For this column, I wrote the following formula:
=IF(OR(AND(CONTAINS("AM", [Start Time]@row), CONTAINS("AM", [End Time]@row)), AND(CONTAINS("PM", [Start Time]@row), CONTAINS("PM", [End Time]@row))), ([End Time Hour Helper]@row  [Start Time Hour Helper]@row) * 60, IF(VALUE(LEFT([End Time]@row, 2)) = 12, ([End Time Hour Helper]@row  [Start Time Hour Helper]@row) * 60, (([End Time Hour Helper]@row + 12)  [Start Time Hour Helper]@row) * 60))
Assumptions:
Sessions are in the same day (none that cross the overnight threshold)
You will write start and end times one of two ways (with either 7 or 8 characters, including spaces). To ensure accuracy, I would add all the hours in the day as drop down values in your Start and End Time columns formatted the same way, assuming sessions start at predictable intervals (i.e. every half hour, 15 minutes, hour, etc.)
Here's a screenshot of what the finished product of these formulas looks like on my screen:
In any case, this was a fun one to figure out. Hope this helps!:)

Wow, Brian, this is incredibly impressive! I definitely wouldn't have figured this out on my own. Thank you so much for your help!

Holy Moly, this is awesome!! And exceptionally helpful! I'm brand new to Smart Sheet but I hope one day to be able to configure formulas like this one. Thanks again 🦄

Glad it was helpful! I'm sure you'll be up and running with formulas like this in no time!:)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!