Calendar with criteria

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
"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
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Oops, I meant in months with 30 days.
 

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 15, 2007
Messages
23,985
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
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 15, 2007
Messages
23,985
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,898
Messages
5,638,886
Members
417,058
Latest member
BRYCEPIETROWIAK

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top