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