Forum Discussion

officerdon's avatar
officerdon
Copper Contributor
Jul 23, 2022
Solved

Auto populate available shifts according to rank

Hello.  Our department has at least 20-40 overtime shifts a month.  We have 13 full time officers ranked by seniority (chief to regular patrolman).  I am trying to create an excel sheet that (once ev...
excel
dscheikey's avatar
dscheikey
Bronze Contributor
Jul 25, 2022

Hi officerdon,

I have now adjusted the table a little so that you can enter numbers or combinations of numbers/letters/special characters.

The donation thing was meant more as a joke. Nobody has to pay or donate anything to be helped here. The support is free and is done by volunteer Excel supporters. Of course the children in St. Jude would be happy about your donation. I am quite sure of that. No matter how much it is.

 

officerdon's avatar
officerdon
Copper Contributor
Jul 26, 2022
That works perfect. Wish I had your knowledge on working excel like that! Question, is there a way to have the requests filled "snaking" through the order of ranks from chief to lowest...that is to say, Chief doesn't always get what he wants...so he gets first pick, then captain, then sgt. and so on. so if chief wants a range of dates, and others want the same, chief gets first pick, then captain (if not taken by chief, then Sgt. if not taken by captain and chief and so forth by snaking through the round, until all shift requests are taken). Essentially it's like a fantasy football draft, chief starts with his desired pick for the first round and it moves down. Round starts at the top (chief) and snakes through to the bottom, then starts back at the top and snakes back down.

Chief: 2nd, 3rd, 4th, 5th (gets 2nd)
Captain: 2nd, 3rd, 6th, 7th (gets 3rd)
Sgt.: 3rd, 6th, 8th, 10th (gets 6th)
Off#1: 4th, 5th, 7th, 8th (gets 4th)
Off#2: 5th, 7th, 9th (gets 5th)
and so forth
  • officerdon's avatar
    officerdon
    Copper Contributor
    Jul 27, 2022
    Not even sure that possible for excel to do for the experts..? But the Twp. did authorize a $100 check for St. Jude on behalf of our PD! So that is a feel good story 🙂
    • dscheikey's avatar
      dscheikey
      Bronze Contributor
      Jul 28, 2022

      Hi officerdon,

      I created a second worksheet using the Snake variant. It was not so easy to solve with Knoff-Hoff, so I had to create an auxiliary column old-scool. Please do not delete the AM column. I have hidden it so that it does not interfere.

      I hope it corresponds to your ideas.

      Thank you also in the name of St. Jude. I hope it will do some good.

       

      • officerdon's avatar
        officerdon
        Copper Contributor
        Jul 30, 2022

        dscheikey 

        Good morning.  I started working on the OT list.  Once I clear out the initial shifts on the left and put the new requests in, the formula stops working in the "snake" book.  In the "chief gets all", it shows "#NAME" as well.   What am I doing wrong?  I believe I initially replied to my post.  Sorry if you get a double reply!