Select separate values from multiselect cells
I have a sheet where staff input their hours available in a week. I have the formula to calculate the hours available for the week. However, staff can enter multiple start and end times, if they are available from 8:00am  10:00am and then from 3:30pm to 5:00pm. The formula still works for the first set of numbers, but doesn't factor in the second. I need a formula that can recognize both time frames, or a way to pull out those second hours and add them in.
Answers

First, it would be helpful to see the full sheet as well as the formulas you have, so I am assuming a bit here.
A couple options...
You could have columns for Monday Start 1, Monday End 1, Monday Start 2, Monday End 2 and only have one value put in each cell then reference the first set and second set (where applicable) to determine availability.
If you really want one column, you can pull out the first set of values using a LEFT formula (5 characters) and the second set using a RIGHT formula (last 5 values). Might have to tweak this to make the math come out using VALUE for example.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

I, too, suggested the separate columns for the times but got shot down! Screen shots are pretty much the sheet, minus a contact column and some test formula columns. Here's the formula: it's kinda scary but it works!
=((VALUE(LEFT([Stop Time Monday]@row, 2))  VALUE(LEFT([Monday Start Time]@row, 2)) + (VALUE(RIGHT([Stop Time Monday]@row, 2)) / 60)  (VALUE(RIGHT([Monday Start Time]@row, 2)) / 60))) + ((VALUE(LEFT([Stop Time Tuesday]@row, 2))  VALUE(LEFT([Tuesday Start Time]@row, 2)) + (VALUE(RIGHT([Stop Time Tuesday]@row, 2)) / 60)  (VALUE(RIGHT([Tuesday Start Time]@row, 2)) / 60))) + ((VALUE(LEFT([Stop time Wednesday]@row, 2))  VALUE(LEFT([Wednesday Start Time]@row, 2)) + (VALUE(RIGHT([Stop time Wednesday]@row, 2)) / 60)  (VALUE(RIGHT([Wednesday Start Time]@row, 2)) / 60))) + ((VALUE(LEFT([Stop Time Thursday]@row, 2))  VALUE(LEFT([Thursday Start Time]@row, 2)) + (VALUE(RIGHT([Stop Time Thursday]@row, 2)) / 60)  (VALUE(RIGHT([Thursday Start Time]@row, 2)) / 60))) + ((VALUE(LEFT([Stop Time Friday]@row, 2))  VALUE(LEFT([Friday Start Time]@row, 2)) + (VALUE(RIGHT([Stop Time Friday]@row, 2)) / 60)  (VALUE(RIGHT([Friday Start Time]@row, 2)) / 60))) + ((VALUE(LEFT([Stop Time Saturday]@row, 2))  VALUE(LEFT([Saturday Start Time]@row, 2)) + (VALUE(RIGHT([Stop Time Saturday]@row, 2)) / 60)  (VALUE(RIGHT([Saturday Start Time]@row, 2)) / 60))) + ((VALUE(LEFT([Stop Time Sunday]@row, 2))  VALUE(LEFT([Sunday Start Time]@row, 2)) + (VALUE(RIGHT([Stop Time Sunday]@row, 2)) / 60)  (VALUE(RIGHT([Sunday Start Time]@row, 2)) / 60)))
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
 123 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!