1. ## Days Off

I have people and each of them get to pick what two days a week they want. For each day I have a set number of days off. The problem I want to avoid is ending up with 2 of the same day as off days for 1 person. I would hope there would be several options to figure this. I would like to use a formula or vba that would either force them to pick a particular day off at some point or stop or warn me at a point, that if I continue I will end up with 2 of the same day as off days. As an example:

I have 168 people, they can pick from these set number

Mondays 95 days-off
Tueday 34 days-off
Wednesday 33 days-off
Thursday 33 days-off
Friday 33 days-off
Saturday 66 days-off

As of now I do a manual count at a certian point, and repeat this several times just not to end up with the problem I described above. Can someone please help?

2. Originally Posted by Jay
I have people and each of them get to pick what two days a week they want. For each day I have a set number of days off. The problem I want to avoid is ending up with 2 of the same day as off days for 1 person. I would hope there would be several options to figure this. I would like to use a formula or vba that would either force them to pick a particular day off at some point or stop or warn me at a point, that if I continue I will end up with 2 of the same day as off days. As an example:

I have 168 people, they can pick from these set number

Mondays 95 days-off
Tueday 34 days-off
Wednesday 33 days-off
Thursday 33 days-off
Friday 33 days-off
Saturday 66 days-off

As of now I do a manual count at a certian point, and repeat this several times just not to end up with the problem I described above. Can someone please help?
Hello
Not sure I understand this. You say that the staff get to pick the days they want off. In that event, why would someone pick the same one for both days off? (they can't take the same day off twice????)
You have 168 people, which is 336 days off, but your set numbers only add up to 294 ???

Thanks
Hercules

3. Okay, thanks for replying. I am sorry I forgot to add Sundays which I have 42 days off. Let me explain better, they can pick any day they want at a certian point if not caught I'll end up with 2 of the same day off. I would like to have something that tells me or stops me before it about to happen. What I do mannualy is when the days start dwindling I count the days to make sure I have to different days off. This is when I have to force them to pick one day and have them pick another day with that. I hope I explained myself a little better. I am planning on creating 4 day work weeks which they will be able to pick 3 days off, so I would increase the days off by 504 for the same amount of people.

4. OK - Let me see if I understand.
On the table below Ive put in some options showing that things get "sticky"
as you run out of days. So I think your talking about avoiding having two persons taking (eg)
the same day off because the last two remaining days are on the same day? What do you gain by avoiding that ?
At that stage you must already have loads taking the same day as someone else?

BTW Im only asking so I can Understand the problem and work out how to help

5. I think that first you will need to identify the situations that you want to be warned about.
Obviously its no good just being told at the time that there is 2 days left on the same day, because its
too late for you to do anything. So you might want a number of warning stages (perhaps) using different
colour formatting.
The best way to do this is probably to draw up some rules based on what your looking for
when you do it manually.

Hercules

6. I hope this helps. If you look at my image. I have an Am group, a mid-day group and a pm group. For each group 2 different groups share the days0off (AM-Run, AM Relief) Based on the amount of people I enter a quota of how many days-off I what to give per day. As the group of people start taking days from the pool the number of remaining days start to dwindle. At the begining of the process, there are no issues, but once I start running out of days because people picked them. I have to stop the pick process and manually count what days are left to ensure each person recieves 2 different days-off. I have to do it manually because the system I use does not catch this. I just would like something to help me, in excel.

7. I think I sort of get the idea now.
I think that you might try conditional formatting to draw your attention to potential problem numbers. For example you might colour your cells, green, yellow amber or red to indicate the level of concern.
To set up the formatting you will need to define the rules about how each colour is used. If you sit and think for a minute you'll probably realise that your already going through this process repeatedly
to spot potential problems. Get something basic in place, and later you can refine it as you go along if you find something its not picking up.
Another idea might be to plot the average take up of days from the pool (daily or weekly) so that if there is a run on a particular date you will pick it up.

HTH

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•