# Calendar with criteria

#### Flagz

##### New Member
Hello all,

The level of knowledge on this site never ceases to amaze me, but I have a feeling this is a stumper. I feel it may just be too much for Excel to handle.
I am trying to create a formula for a calendar. I have 10 departments labeled 1 through 10. Each department must do a random audit at least twice a month,
Sunday to Saturday. I want it set up so that the same department is not audited 2 days in a row, and only once per week. I am using the Excel template,
"Any year calendar(single month per tab)".
I created a sheet called Departments. In cell A2 I entered DEPT1, in A3 I entered DEPT2 etc, and worked my way down to A32 to represent each day of a month.
I created in Column B, a heading "JAN 2020" and entered the numbers 1 to 31 to represent each day of a month, then in column C I entered a RAND function to randomly sort the numbers in Column B.
I continued this across the sheet until each month was represented.
Having trouble developing a formula that will:
A) sort each month randomly
B) have a department audited only once per week
C) have a department audited at least 2 times per month

I hope I have explained this well enough, but I fear I haven't. Thanks

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### mikerickson

##### MrExcel MVP
"Each department must do a random audit at least twice a month,
Sunday to Saturday. I want it set up so that the same department is not audited 2 days in a row, and only once per week."

How often is a department audited? once a week (4 times a month) or 2 times a month?

You have more departments than days in the week, so if each department is audited once a week, there will be days when more than one department is being audited.

#### Flagz

##### New Member
I stipulated at least twice per month because in days with 30 days each department will be audited 3 times, but in February, for example
some will be audited only twice. No more than twice a week, so if Dept 1 is audited on Sunday, it cannot be audited again in that week,
and optimally not again until all the other departments have been audited at least once. So optimally, each department, once every 10 days.
Thank you

#### Flagz

##### New Member
Oops, I meant in months with 30 days.

#### Flagz

##### New Member

This problem I am having is making it random, and adding the criteria of once every 10 days, and not 2 times in a row.
for example Dept 1 could be audited day 10 of the month, so the once every 10 days is good to go, but then be randomly
chosen again for day 11, so it is audited 2 days in a row, and twice in one week. That is the reason for the criteria.

#### mikerickson

##### MrExcel MVP
Try this
Put dates in column A
Put 1,2,3,4,5,6,7,8,9,10 in C1:L1

Then use this UDF with the formula
=randomfrom(\$C\$1:\$L\$1,B1:B7) in B8.
Then drag down.

VBA Code:
Function RandomFrom(rngOptions As Range, rngExclude As Range)
Dim arrOptions() As String, Pointer As Long
Dim randPointer As Long, oneCell As Range
With rngOptions
ReDim arrOptions(1 To .Cells.Count)
For Each oneCell In .Cells
If Application.CountIf(rngExclude.Cells, oneCell.Value) = 0 Then
Pointer = Pointer + 1
arrOptions(Pointer) = oneCell.Value
End If
Next oneCell
End With
Randomize
randPointer = Int(Rnd() * Pointer) + 1
RandomFrom = arrOptions(randPointer)
End Function

This approach knows nothing about months or weeks. All it does is randomly select a department that hasn't been selected in the prior seven days.

#### Flagz

##### New Member

Thanks mikerickson! Not very familiar with using VBA but I will do a little more reading. Cheers

#### Flagz

##### New Member
I came up with this formula but it isn't doing the trick
= IFERROR(INDEX(DEPT!\$V\$2:\$V\$32,MATCH(B11:H11,DEPT!\$V\$2:\$V\$32,0)),0)

the DEPT! is the name of the sheet where I have the month and day with the RAND formula
The B11:H11 is the reference in the calendar that shows the day. For example if July 1st was on a Wednesday
the box Wednesday would have a 1 in E3

#### mikerickson

##### MrExcel MVP
You pretty much have to use VBA for this. The formula's that use RAND or RANDBETWEEN are volatile. as soon as you change any cell in the worksheet,you're going loss the result of the formula, so all of last week's choosen departments all go away or change.

Replies
7
Views
139
Replies
3
Views
68
Replies
16
Views
659
Replies
0
Views
108
Replies
1
Views
75

1,130,151
Messages
5,640,420
Members
417,142
Latest member
andygame

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back