Using Excel Solver to build rosters

[GB]

Hi All, I have been trying to use Excel Solver to write what should be a simple solution but I do need assistance to get me started.

My Goal is - create enough 28 day roster profiles (with work days and days off) that meet the following rules. Each roster line must:

1. contain 8 days off in 4 lots of day off pairs, and
2. paired days off can't be less than 3 days apart, and
3. paired days off can't be more than 6 days apart, and
4. contain a minimum of 1 weekend off, and
5. the "Total Work Days" (blank days) must be equal to or exceed the required "Daily Work Requirement"

Can someone please teach me how to achieve this. I have provided a file which shows some sample rosters. Any header with a green cell background indicates that formulas exist in that row or column.
regards
GB

 

[GB]

Hi All, reaching out again to see if anyone can help with the problem above. Any solver experts amongst the group?
Regards
GB

 

[p45cal]

The attached has a macro (blah) to populate cells C7:AD352 with all possible pairs of days off with the constraints of 3 to 6 day gaps before, between and after each pair of days off.
The macro first puts pairs of Os, then at the end puts a W in all the blank cells (so it's a good idea to blank ALL the cells in that range before running).
I'll let you do the filtering for 'at least one weekend off' and I don't understand point 5.
I don't know how I'd do this with Solver.

 

[GB]

Hi @p45cal thanks for your help, much appreciated.

• In relation to Point 5 above (sorry not clear) - The "W" days must not be less than the "Daily Work Requirement", therefore if too many "O" days are given on a particular day then there is not enough staff to cover the work (W).
• I opened the file but your macro (blah) is not present. Can you please resend.

regards
GB

 

[p45cal]

I messed up there! I had to rewrite:

Sub blah()
Set TopLeftCell = Range("C7")
rw = TopLeftCell.Row
colm = TopLeftCell.Column

TopLeftCell.Resize(346, 28).ClearContents    'line added after I knew how big the range to clear was going to need to be!

For g1 = 0 To 6
  For g2 = 3 To 6
    For g3 = 3 To 6
      For g4 = 3 To 6
        If (g1 + g2 + g3 + g4) < 21 Then    'last pair would be beyond 28 days
          If (g1 + g2 + g3 + g4) > 13 Then    'would leave final gap more than 6 days
            Cells(rw, colm + g1).Resize(, 2) = "O"
            Cells(rw, colm + g1 + 2 + g2).Resize(, 2) = "O"
            Cells(rw, colm + g1 + 2 + g2 + 2 + g3).Resize(, 2) = "O"
            Cells(rw, colm + g1 + 2 + g2 + 2 + g3 + 2 + g4).Resize(, 2) = "O"
            Cells(rw, "AH") = g1 + g2 + g3 + g4
            rw = rw + 1
          End If
        End If
      Next
    Next
  Next
Next
Range(TopLeftCell, Cells(rw - 1, colm + 27)).SpecialCells(xlCellTypeBlanks).Value = "W"
End Sub

Beware, it works on the active sheet.
You'll have to amend your various formulae on sheet to match the larger ranges.
In the code, g1 to g4 are the 4 gaps:
g1 OO g2 OO g3 OO g4 OO
g5 looks after itself.

 

[GB]

Thanks @p45cal I will try this and see how it goes. Cheers GB

 

[p45cal]

Just noticed, the line beginning:
Cells(rw, "AH") =
shouldn't be there.