Select separate values from multi-select cells

AnneV

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.

SoS | Dan Palenchar

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.

AnneV

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