I am making a roster and schedule for my fire department. I have most of it done and now I'm stumped. I've played around with index and matching, but I haven't been able to make it work for me yet. There might be another way that I don't know of. The reason for needing this is we swap shifts quite often and this will make keeping up with changes much easier.



Page 1 of my workbook, named Roster, has a roster and a number assigned to each person. The number represents that persons regular day off(RDO).


On page 2 of my workbook, named Calendar, I have a list of the people that are supposed to be on duty for each day of the year. What I need is when number "1" shows up on a day it fills in a cell that says shawn is off, when "2" shows up, jim is off etc. And this needs to apply to each shift. And there are two names assigned to each number per shift...so confusing.


I hope my question make sense. I have included an attachment with part of my calendar. The bottom 2 empty cells on the Calendar page need the names associated with the particular "RDO" number. Thanks ahead of time for helping me out.

Attached Files

Calendar Example.xlsx (10.3 KB, 18 views)

A few questions:

Does it matter who shifts with who, or do you not care as long as a person only takes a certain number of days off per period?

Is the RDO per month or?

Does the order of the persons on the schedule have any significance?

I've taken a different approach to the schedule you created.

It's fairly simple and assumes a working shift has the value 0 and non-working is 1.

I have populated the sheet with random 0 and 1.

If a person has too many RDO's the name is highlighted with red.

/Møller

Attached Files

Calendar Example (1).xlsx (23.2 KB, 11 views)

Have added the option of specific days off with the value 2.

So for each shift the value 0, 1 or 2 is entered and the color of the cell changes accordingly.

For RDOs I have set it up so the value 2 should be entered for both shifts (entering only a single 2 will sum as a half RDO).

/Møller

Attached Files

Calendar Example 2.xlsx (37.4 KB, 8 views)

Right now the shifts are set. But we do move people around more often than we like.

We work 48 hours on, then 48 hours off. The RDO is a rotating day off that happens once every 7 shifts. Every 2 weeks, we get a RDO, which gives us an extra day off. Then every 3 months, we will get 2 RDO's that are back to back which gives us either 4 days off, or 6 days off. It's pretty confusing until you get used to it.

I had the order of the roster by rank, highest rank at the top.

What I'm trying to accomplish is make a calendar that shows who is on shift every day, and who is on RDO every day. Any changes to the roster will auto populate to the calendar portion.

I might be overthinking this and making it more complicated than it has to be. I could just tell the Calendar to look at the specific cell next to the RDO number.

You are going to run into problems when you try your solution, because you'll have to manually delete people from the calendar when they swap days off. Additionally with your somewhat complicated RDO pattern it becomes really complicated.

Look at the attached calendar.

It's an alternative that who is working on any shift (green), who is not working (black) and who is on RDO (blue). I have assumed half the staff is on shift A and the other half is on shift B (and ignored ranks).

If I understood you correctly you (ought to) work in the following pattern (ignoring the monthly extra RDOs): 6 days on - 1 day off - 5 days on - 2 days off - Repeat.

Let's say Bob needs to take 05-Jan off. He can then swap with Ethan, Greg, Ross and Russ who have an RDO on that particular day. To swap with e.g. Ethan, you would enter the following values:

Bob
05-Jan in shift A: 2
05-Jan in shift B: 2

Ethan
05-Jan in shift A: 0
05-Jan in shift B: 1

I'm guessing that Bob and Ethan would have to swap another shift otherwise Ethan would loose out but it is the same principle.

You can add more weeks by simple copying.

Try to populate it with actual data and see if it does what you want.

/Møller

Attached Files

Calendar Example 3.xlsx (67.3 KB, 12 views)