Who is available right now? Tomorrow?
March 25, 2015 8:26 PM   Subscribe

I manage a large-ish team of volunteers, each with different schedules. I have information for all of them in an old fashioned spreadsheet so I can easily sort them by name, age, etc. Now I need a way to put together a Master schedule of sorts so I can easily see who is available for a task at any given time (and how many are available). Some considerations inside.

I interviewed every volunteer and they all told me when they're available to work for us (like "Monday mornings", "Weekdays after 5 PM" or "Only on weekends"). These schedules are mostly static for the purposes of this question.

We have five group leaders and every one of them will get a team. What I'm looking for is an easy way for me and the team leader to see who and how many are available at any given time (say, "right now", Tuesday mornings" and so on). This system would also double as a way of scheduling tasks so that we don't plan a work-intense task when no one is available

I understand that I could append an "Availability" column to my master spreadsheet to see the schedule for every one, but then I'd have to manually search in every single cell for matching schedules and I believe there should be a better way.

Bonus points if it's done with(in) common spreadsheet software. We can spend money on software/hardware but since we're a non-profit, we must maximize the bang for our buck. The AskMeFi robot tells me this, this and this are related questions. They provide me with some answers, but I don't see anything in there addressing this specific function.

In a sci-fi universe, this would look like a 24x7 grid, colored to indicate density of available volunteers (red having a lot of people available, blue having none); I would touch the screen and a list of people would come out.
posted by andycyca to Technology (11 answers total) 8 users marked this as a favorite

Alternatively, at least as described here, this is like a hundred lines of code, including parsing schedules out of a CSV file. A good CS major can write you a bare-bones tool overnight for a bottle of Glenfiddich 18.
posted by d. z. wang at 8:40 PM on March 25, 2015

Longer & lazier way, assuming you're using Excel - you could add a column for each day of the week, and then in the row for each volunteer (assuming their names are all in a column), write the times they're available for each day, leaving blanks when they're not available. Make all that a table. Then when you want to see who's available on Monday, use the drop-down arrow at the top of the column for Monday to apply a filter (you could untick "blanks" and/or select whatever times you need), and only the available volunteers will appear. You can print that list.
posted by cotton dress sock at 9:05 PM on March 25, 2015 [1 favorite]

Would using a Google Calendar work?
posted by AppleTurnover at 9:35 PM on March 25, 2015 [2 favorites]

Best answer: Do you use pivot tables? I feel like you could pivot table your way out of this. Basically, pivot tables allow you to take info from a large spreadsheet and instantly ncross reference it however you like. Say you have a sheet with a rows of customer names, and coulms of transaction dates and sale amounts --- throw it all in a pivot table and you can show the number of transactions on a given date, average number of transactions per customer, total sales per customer --- most everything you have in the columns on the big spreasheet you can rearrange. If you were to put the schedule data in a few coulms, however it makes sense to do that, it seems to me you could pretty easily create a table that would show at a glance, say, how many volunteers are availible Monday mornings, etc.
posted by maggiepolitt at 10:48 PM on March 25, 2015 [1 favorite]

Best answer: maggiepolitt's pivot table idea is a good one, though there will be some startup costs. You will want to create a row in your spreadsheet for each one of each volunteer's free time blocks. Like this (| means a new column):

Alex | Monday | 6
Alex | Tuesday | 12
Alex | Sunday | 3
Bryan | Thursday | 4
Bryan | Friday | 4
Bryan | Friday | 5

And so on. With the information organized this way, you'll be able to pivot table your way to virtually any representation of free volunteers you could imagine (including your 24x7 grid idea).
posted by telegraph at 4:06 AM on March 26, 2015

Have you tried or looked at any of the Volunteer apps in the Apple App Store? It looks like some of them might provide the precise functionality that you are looking for. In particular, Volunteer Schedule Pro appears to be precisely what you want.

It's not free and I don't know what your budget is but, frankly, the tool looks amazing. I'm actually going to forward it to a couple of organisations here to see if we could roll it out.
posted by HopStopDon'tShop at 8:11 AM on March 26, 2015

Best answer: We have an Excel file set up with one sheet for each day, one person per row, and one half-hour block per column, and colored blocks for available hours. So if you want to see who's available at, say, 10:30 on a Wednesday, you just go to the Wednesday sheet, highlight the 10:30 column, and you can see quickly by which blocks are colored in, who is available. Takes a little time to set up but it's pretty handy.
posted by rabbitrabbit at 8:30 AM on March 26, 2015 [1 favorite]

Or if you're a visual learner, here is a quick demo I just made up.
posted by rabbitrabbit at 8:36 AM on March 26, 2015

It seems like you might find When to Work useful if you don't mind paying. All your volunteers would put in their availability (Cannot Work, Dislike Working, Prefer Working, No Preference) for a repeating weekly schedule (and can also do specific date availability. When you go to schedule a task, it'd show you everyone's availability and how many people prefer working at that time.
posted by Deflagro at 12:46 PM on March 26, 2015

Response by poster: Many answers! I'll check each one and reply right here in a few minutes
posted by andycyca at 5:17 PM on March 26, 2015

Response by poster: Well, that took more than a few minutes. I believe that, for this particular setting, pivot tables are the way to go, but the apps are better for long term management. I still have to master the tables, but I'm finally seeing what I need to do. thank you everyone!

Consider this case closed.
posted by andycyca at 6:44 PM on March 28, 2015

« Older Are there any films similar to Alfred Hitchcock?   |   More hair help needed Newer »
This thread is closed to new comments.