Select separate values from multi-select 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
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!