Hello! Thank you everyone in advance for your help. I use Airtable to manage the availability of a little over 100 therapists who work for my company. Most of these people work part time and, thus, have wildly different working hours. I have a formula field that calculates their total daily available work time and that field is summed within their group so we also have access to their weekly availability.
The Max 30 Min Sessions/Day column uses this formula: ROUND(((({Hours per Day}*.6)/3600)*2),0)
to calculate the maximum number of 30 minute sessions that fit into 60% of their daily availability. (They need work time outside of therapy sessions.) In general we’ve been using this formula to determine how many students maximum a therapist can be assigned. So my first question is: does this formula actually tell us how many 30 minute sessions fit into 60% of a person’s day? Or does it need to be changed?
We use the same general formula to calculate a therapist’s capacity for seeing students in groups of two. Theoretically, a therapist can be more efficient with their direct time seeing two students at once, but will need more indirect time so the formula takes into account that we only want to schedule 43% of their time. The formula looks like this: ROUND(((({Hours per Day}*.43)/3600)*2),0)
Brilliant. Now things get trickier. We need to know (for staffing reasons) how many students each therapist can have on their caseload, but also how many more we can accommodate. So we use a formula in another column to calculate individual student capacity based on the number of sessions the therapist is already scheduled with: {Max 30 min Sessions/Day}-{Actual Sessions}. Basically, this assumes that 1 session = 1 student and everything is simple. When I try to use that same formula for groups of two students to calculate how many groups of students we can accommodate I often get negative numbers.
Here’s the formula: ({Max Group Sessions/Day}-{Actual Sessions})*2
Second question: How can I change the formula to show an accurate calculation of the number of students she can see when they are grouped?
Right now, the way it works is that as sessions/students are scheduled with a therapist, their calculated availability goes down. That is a very helpful metric for us to have, so any solution needs to accommodate that function. If you’ve read all the way down here to the end, I would like to thank you for your patience and humbly implore your help. Thank you so much!
