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.
👨🏼💻 Dan Palenchar  School of Sheets Solutions Consulting  Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
 ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS  If you have a follow up response tag me @SoS  Dan Palenchar so I get notified of your reply!

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
Check out the Formula Handbook template!