Calculating available hours based on multi-select dropdown

kolfinna

Hey all,

I'm working on building out a scheduler for people; on the signup form, they have options like:

Monday 8a - 12p

Monday 12p - 4p

Monday 4p - 7p

...and so on through Friday, plus some Sat/Sun weekend shifts.

What I'd like to do is calculate the Total Available Hours and use that as a reference point to pull from after 30min sessions have been booked.

So far I've set up a vlookup sheet where the first column are the criteria, and the second column has the included number of hours e.g., 4 for Monday 8a-12p.

Then in the original sheet, I've used COUNTM to count the number of shifts available.

Is it then possible to somehow combine those two in order to multiply # of shifts * vlookup of the hours within said shifts?

Or is there an easier way? :D

Thanks!

Paul Newcome

Are you able to provide screenshots with sensitive/confidential data blocked, removed, and/or replaced with mock data as needed? It would also be helpful if you could provide a screenshot of what you are trying to accomplish with the data manually entered so that we can see your end goal.

kolfinna

Can do; I'm currently working with dummy data anyway. :)

Here's a snippet of the Active Tutors sheet:

Total # of sessions is the countm formula. If every session were 4 hours, it'd be super easy. However--weekend shifts are 2h and every evening there is a 3h shift from 4p-7p, so I'm trying to account for that.

Here's a shot of the vlookup sheet:

So basically the formula would be to vlookup each of the multi-select criteria, and add the # of ours together, multiplied by 60 to get the minutes.

There's a separate tracker here that will subtract from the total, using another sort of vlookup or match with cell linking:

From here, I'd count the total duration at the parent row, then have a column in the Active Tutors sheet to count remaining weekly minutes.

Then - the Tutor Schedule would be presented as a calendar so we could see at a glance who's available for any given day...but only the available time slots. Yeesh. :D

Upside, we're vetting companies to provide this scheduling capability, but that might be a month or two out and we need this functionality a lot sooner than that.

Paul Newcome

Is each tutor only signing up for 1/2 hour at a time?

kolfinna

Yes, and if they need more than 30min they'll have to book two sessions in a row. Besides--I don't think younger kids can focus much longer than that. :)

Paul Newcome

So if I wanted to sign up for a spot, what do I do? What do I see?

kolfinna

The basic workflow is:

1. Tutor accesses a form to fill out their details and type of tutoring they want to do (e.g., paid tutoring hours, field hours, or both)
2. We get notified upon submission of their information; we contact them for any additional questions and ensure we document the number of paid hours and capture their rate in an Active Tutors (Summary) document.
3. Overnight, the tutor name is copied to the schedule and the rows are moved to the Active Tutors sheet. The Tutor Schedule has a vlookup (which should probably really be an index/match to the Active Tutors sheet) to bring over all the relevant remaining info into the Schedule, so our scheduling team has a single pane-of-glass view. They should be able to filter down tutors by subject need, then view availability from there.
4. The Scheduling team will look at the tutor's available hours and break those selected shifts out into 30min increments, nested under each tutor's name in the schedule; I have some helper columns placed to identify day of the week based on the start date as well as a helper column to calculate [Start Date]+7 to provide the next week's day for scheduling ease.
5. Scheduling rep will then look at the student intake form and begin pairing students with tutors based on subject areas of need; ideally we'll keep students to as few tutors as possible to avoid bouncing among virtual meeting sessions. However, the rep will also need to check with parents on scheduling availability and session preferences--i.e., do they want a block of tutoring or a couple sessions each week, etc.
6. Empty rows in the Tutor Schedule are also shown so that when looking at a calendar view, the scheduler can double-click a timeslot and book a time (though the grid view may be easier once we have several tutors lined up). At this time because I don't know of any recurring task capability that builds out dynamically within an indented set of rows, the team will have to add the next two months' worth of dates manually. Hoping the helper columns are useful here; at the same time, I've built out an example schedule at the very top--they can copy/paste the rows, and formulas should auto-populate from there. I've also considered a helper worksheet to break the shifts out into something a lot cleaner than what is currently in the schedule; the scheduler would look at that, pick the start day, and then the rest of the dates would automatically update accordingly, taking the pain out of manipulating the actual Schedule.
7. Once the tutoring time/dates have been set, the rep then sends an email to the parent/guardian/caring adult and the tutor providing the introduction. They'll mark in the student intake form that all subject needs have been paired with one or more tutors, and then that information will be moved to an Active Student sheet--thus keeping only the students who still need tutor pairings in the Student Intake sheet.
8. Automation will run daily to archive duplicate entries as well as remove inactive tutors.

Beyond all this I still need to build out the metrics, but thankfully that's less of an immediate need and more of a "nice to have."

Does this help?

Paul Newcome

What are the actual form options?

kolfinna

Here's a snippet from the tutor intake form:

The Hours Available is a multi-select dropdown. Maybe there's a better way to capture this information so there's a 1:1 match between shift and number of hours included?

Thanks!

Paul Newcome

So if I wanted to sign up for Monday from 8a - 9a, I would submit two forms with the "Monday 8:00 am-12:00pm" option selected on each?

Paul Newcome

kolfinna

So--good news--the team has elected to go to 1h increments instead of the variable--that makes things much tidier when it comes to using an mcount formula to count all selections in the session availability cell.

Thanks anyway, though!

Paul Newcome

Glad you have a working solution. 👍️