Results 1 to 7 of 7

Thread: Days Off

  1. #1
    Neophyte Jay's Avatar
    Join Date
    Nov 2013
    Location
    Florida
    Posts
    3
    Articles
    0

    Days Off



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Jay View Post
    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 ???
    Please can you clarify.

    Thanks
    Hercules

  3. #3
    Neophyte Jay's Avatar
    Join Date
    Nov 2013
    Location
    Florida
    Posts
    3
    Articles
    0
    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. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    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


    Click image for larger version. 

Name:	Table.jpg 
Views:	13 
Size:	63.0 KB 
ID:	1850
    Last edited by Hercules1946; 2013-11-26 at 04:16 PM.

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    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.
    How is your data organised? Can you post an example spreadsheet?

    Hercules

  6. #6
    Neophyte Jay's Avatar
    Join Date
    Nov 2013
    Location
    Florida
    Posts
    3
    Articles
    0
    Click image for larger version. 

Name:	Days Off.PNG 
Views:	14 
Size:	75.6 KB 
ID:	1852I 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. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    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
  •