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
officerdon's avatar
officerdon
Copper Contributor
Jul 25, 2022
This works absolutely PERFECT!! Is there a way that the shift available can also have letters and special characters as well? We work in the hospital too so a shift would be like "20th-3H". Other than that, Incredible!! I was thinking St. Jude unless you have a request that our Department could donate to. Thanks again!
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.

         

Resources