Forum Discussion
Formula that will check a series of cells and flag if someone can be scheduled at that day/time
Hello Experts,
My org has a lot of fluctuation in team assignment and scheduling. I'm looking to build what is essentially a schedule checker. Here are the details:
I have a table with days of the week across the top and time throughout the day in 30 minute increments. What I'm trying to do is set it up so if I choose a name from a data validation dropdown it will reference their shift start and end, their days off, and then flag in this calendar whether they are available or not for that segment.
I'm not sure what the best way to do this would be. Presumably a vlookup can be used to pull data from the cells containing their schedule and days off, but I'm, not sure how to have it check against all the other variables to populate a yes or no result.
I'm currently using =IF(AND(L2>=MIN(B2:C2),L2<=MAX(B2:C2)),"Yes","No") To flag a yes result based on the time, with the L Column representing the half hour segments and checking against the start times (Column B) and end times (Column C), but that still leaves out days off.
Any thoughts on this?
3 Replies
Lots of thoughts. A few questions too. And a request.
Let's start with the request: would you please post a copy (or a facsimile of the real thing, absent real names of real people) of what you have for employees' already committed schedules? Would you also post a copy of the table you're trying to fill, the one with days and 30 minute segments" Post these files on OneDrive or GoogleDrive and paste a link here that grants us access. [In the absence of being able to work with your files, you're in effect asking us to recreate the whole system, or just offer generalities]
Questions:
- Are you planning to assign people to 30 minute blocks? Wouldn't you want a minimum of X hours, made up, perhaps, of 30 minute blocks....is something like that a part of the design here?
- You describe a scenario of picking a name and the system--however it works--checks to see if they're available for a given block of time? Might it not make sense to begin with the block of time that has to be filled, and then go look at all the employees to see which ones are free and assignable?
- In general: maybe give as much as possible about the big picture here, what the business is, the constraints and goals....don't worry about speculating whether it's VLOOKUP or whatever--let us come up with the formulas; you concentrate on describing the "business" procedure in words.
Questions:
Are you planning to assign people to 30 minute blocks? - For the purposes of this, yes. Each person has a set schedule of working hours. I am simply trying to build a tool to quickly and efficiently build a coaching schedule for a given team. Each person gets one 30 minute coaching peer week, and nobody has the same scheduling or days off.
Wouldn't you want a minimum of X hours, made up, perhaps, of 30 minute blocks....is something like that a part of the design here? - No, because we are simply plugging in a 30 minute coaching on a pre-set schedule.
You describe a scenario of picking a name and the system--however it works--checks to see if they're available for a given block of time? Might it not make sense to begin with the block of time that has to be filled, and then go look at all the employees to see which ones are free and assignable? - I would be fine with this, so long as it could check whether they were available during that window on that day.
In general: maybe give as much as possible about the big picture here, what the business is, the constraints and goals....don't worry about speculating whether it's VLOOKUP or whatever--let us come up with the formulas; you concentrate on describing the "business" procedure in words. - Apologies for not including enough overall information. Hopefully I've remedied that with this update.
Helpful answers to the questions. In the process, though, you neglected responding to the request.
Let's start with the request: would you please post a copy (or a facsimile of the real thing, absent real names of real people) of what you have for employees' already committed schedules? Would you also post a copy of the table you're trying to fill, the one with days and 30 minute segments" Post these files on OneDrive or GoogleDrive and paste a link here that grants us access. [In the absence of being able to work with your files, you're in effect asking us to recreate the whole system, or just offer generalities]
