Forum Discussion
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 everyone picks any of the OT shifts they can work, ranked from their most wanted to least) automatically assigns them those dates picked, BUT the senior man would get a date if a less senior (ranked) man picked it also.
In example, there are 20 shifts available, 3p-11p on the 10th through the 29th.
Chief wants the 10th, 14, and 20th
Captain wants the 14th, 21st and 22nd
Sgt. wants the 11th, 21st and 29th
Officer #1 wants the 10th, 11th, 13th and 23rd
Officer #2 wants the 13th, 23rd and 24th
Officer #3-10 same idea.
In this model, Excel would automatically assign:
Chief all 3
Captain, 21st and 22nd
Sgt. 11th and 29th
Officer #1 13th and 23rd
Officer #2 the 24th
and so on for Officer #3- #10.
Is this possible once all dates that are available are entered in a list?
I am using Microsoft 365. Thanks for your help (I'd even pay to have this setup. lol.)
Please see how far my enclosed solution corresponds to your ideas.
You can then donate the money to a charitable institution.
Please see how far my enclosed solution corresponds to your ideas.
You can then donate the money to a charitable institution.
- 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!
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.
Thank you! I will check this out. It is greatly appreciated!dscheikey
