Hello.
I am trying to use excel to schedule on-call employees.
There are 7 employees, each assigned an ID# from 1-7. I then populate a sequential list of dates out from today to 3 months in the future (for arguments sake cells A1:A90).
In cell B1:B90 should be the employee assignments. However, i have to contend with booked vacations and I don't want people to be on call within two days of their last on call. Thus, in columns C:G i put in who has requested the day off (if employee 1 and 2 want the day off i would enter 1 in C1 and 2 in C2).
I was sort of able to make this work (with help from the forum) by using this function:
What that allows me to do is for each row in column B, randomly assign a number between 1 and 7 (or any range i pick), however exclude from that range the the two rows in B above the cell of interest (the last two people who were on call), and also exclude the range to the right of column B (vacation requests). It works (and if i could post attachments i would upload the spreadsheet).
However, the issue is that it is "random". Ideally, i would like to as equitably as possibly distribute the work assignments subject to the constraints so that when i count how many times each employee is on call for the period they are more or less equitably distributed. Through the 90 cells i would like to see as equally as possible the same number of 1,2,3,4,5,6,7... A randomized distribution would probably work fairly well if i scheduled a year out, but on the 3 month window there is too much variation.
I don't know if excel is the animal for this or not. Does anybody have any suggestions?
thank you all.
Justin
I am trying to use excel to schedule on-call employees.
There are 7 employees, each assigned an ID# from 1-7. I then populate a sequential list of dates out from today to 3 months in the future (for arguments sake cells A1:A90).
In cell B1:B90 should be the employee assignments. However, i have to contend with booked vacations and I don't want people to be on call within two days of their last on call. Thus, in columns C:G i put in who has requested the day off (if employee 1 and 2 want the day off i would enter 1 in C1 and 2 in C2).
I was sort of able to make this work (with help from the forum) by using this function:
Code:
Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude1 As Range, Exclude2 As Range) As Long
Dim R As Long
Dim C As Range
Dim JoinRange As Range
Set JoinRange = Union(Exclude1, Exclude2)
Do
R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))
For Each C In JoinRange
If R = C Then Exit For
Next C
Loop Until C Is Nothing
RandBetweenInt = R
Application.Volatile
End FunctionWhat that allows me to do is for each row in column B, randomly assign a number between 1 and 7 (or any range i pick), however exclude from that range the the two rows in B above the cell of interest (the last two people who were on call), and also exclude the range to the right of column B (vacation requests). It works (and if i could post attachments i would upload the spreadsheet).
However, the issue is that it is "random". Ideally, i would like to as equitably as possibly distribute the work assignments subject to the constraints so that when i count how many times each employee is on call for the period they are more or less equitably distributed. Through the 90 cells i would like to see as equally as possible the same number of 1,2,3,4,5,6,7... A randomized distribution would probably work fairly well if i scheduled a year out, but on the 3 month window there is too much variation.
I don't know if excel is the animal for this or not. Does anybody have any suggestions?
thank you all.
Justin
