Random Testing by Month

OK, non-VBA means here that we can fullfill the requirement, but as a month is not 4 weeks long the distribution may not always be "perfectly uniform".

Let's start from the observation that there is always not more than 5 weeks with working days in a month. (sometimes just 4 but then it will be an easy task to "simplify" the procedures.

HAve a look in attached sheet in rows 2 and 3 are week starts(mondays) and week ends(fridays) for given month. Columns G:I are "middle weeks containling both Monday and Friday, columns J:K month start/end weeks (may start later than Monday or end earlier than Fri)
in row 4 is the length of particular week (just deducting start from end and adding 1)
for 4_tests_a_month_person three dates can be generated as:

Formula:

=G$2+RANDBETWEEN(1,G$4)-1

(write in G7 and copy to next 2 cells).
Here generation of random number is integrated with further calculations. Monday date is added random number between 1 and 5 and deduced 1 (we could also generate number between 0 and 4).
Fourth random number is generated in seprate cell K7 as:

Formula:

=RANDBETWEEN(1,J$4+K$4)

so for current month it is between 1 and 7, and the generated date is calculated in J7 as:

Formula:

=IF(K7<=J$4,J$2+K7-1,K$2+K7-J$4-1)

now we can sort the dates in ascending order. in B7:

Formula:

=small($G7:$J7,column(A$1))

and copy right
As we used mixed and relative addressing the formulas are ready to copy down 150 rows (I did only 4 more)

Similar approach as with generation of 4th date can be used to generate dates in 2_times_a_month_person
so in I15 and J15 we can generate random numbers between 1 and 10 (two first full weeks) and 1 and 12 (all other weeks)

Formula:

=RANDBETWEEN(1,G$4+H$4)

=RANDBETWEEN(1,I$4+J$4+K$4)

formula for G15 (refering to I15 is identical as above, and H15 is very similar - we have to check if J15 is smaller than end of first week, second or third.:

Formula:

=IF(J15<=I$4,I$2+J15-1,IF(J15<=I$4+J$4,J$2+J15-I$4-1,K$2+J15-I$4-J$4-1))

and copy down for another 9 persons (B15 is again small and COLUMN but now we look only into 2 columns).

Press F9 to recalculate a sheet , copy generated dates and paste them special as values (and date formats) to another shhet. These dates are generated, so will change every time the spredsheet is recalculated.

First lets clarify. Month is not 4 weeks long. Address how it shall be treated. Do we test just each person from First group weekly (so some months 5 times a month)?

PS. as you want to test 22 each day out of 150: does it mean that you test also on weekends?

Note that my solution from post #2 was only for working weekdays (mon-fri). HAs to be ammended to cover whole week. And it is necessary to define the week start - is it Sunday (american style) or Monday (european)?

VBA would be the best solution.
It is reasonably easy to fill semi-randomly a range by formula with one condition, but with 2 conditions it is not that easy.

As I'm trying to understand the "core" of problem - let's have a look 2 weeks back.
Mr. Brown was tested on September 30th (Wed) - Can he be tested again on Oct 1st (Thu), 5th(Mon), today (12ve-Mon), 31st (Sat)? and then on Nov 2nd (or if you start a week on Sunday - even on Nov 1st?).

OK, so let's do it in a simple to follow (not the quickest method, but less than a second is probably anyway acceptable.
Most of the tesks is really done in a worksheet where in columns J ... main procedure works. The key column is M where for a given date from column J a semi-random number is obtained from formula:

Formula:

=IF(COUNTIF($O$1:$O$4,K1)>0,100,0)+L1+RAND()

(stored in code in R1C1 form):

Formula:

=IF(COUNTIF(R1C15:R4C15,RC[-2])>0,100,0)+RC[-1]+RAND()

so it is a random number + counter of persons who are already assigned for that day + 100 if the person has already a date from this week (as noted in O1:O4).
then lowest value is promoted to very top by sorting

HAve a look how it works in the attachment. To observe it better you can comment-out a line with application.screenupdating and set a breakpoint in a
next j instruction (take next person) and run the code with either <F5> or "play" button - see the screanshot

requirements - one of dates for the mionth to be prepared in A1, list of persons in column A (A3 down) , their number of times to be tested in given month in column B.

The whole code:

Sub test()
Dim i As Integer, j As Integer, t As Double, mycalculation As Integer
Dim month_start As Date, days_in_month As Integer, times_a_month As Integer

'setting stage
't = Timer
month_start = Range("A1") - Day(Range("A1")) + 1
days_in_month = DateSerial(Year(month_start), Month(month_start) + 1, 1) - month_start
Columns("D:O").ClearContents
Application.ScreenUpdating = False
mycalculation = Application.Calculation
Application.Calculation = xlCalculationManual
For i = 1 To days_in_month
  Cells(i, "J").Value = month_start + i - 1
  Cells(i, "K").Value = Format(month_start + i - 1, "ww")
  Cells(i, "L").Value = 0
  Cells(i, "M").FormulaR1C1 = "=IF(COUNTIF(R1C15:R4C15,RC[-2])>0,100,0)+RC[-1]+RAND()"
Next i
' main loop - for each person
For j = 3 To Cells(Rows.Count, "A").End(xlUp).Row
' empty used for this person weeks list
  For i = 1 To 4: Cells(i, "O").Value = "": Next i
' select rarely used date
  For i = 1 To Cells(j, "B")
'sort using current values in column M
    Range("M1:M" & days_in_month).Calculate
    Range("J1:M" & days_in_month).Sort key1:=Range("M1"), order1:=xlAscending, Orientation:=xlSortColumns, Header:=xlNo
' write it to columns D:G, increase date counter, store used week number
    Cells(j, i + 3) = Range("J1")
    Cells(j, i + 3).NumberFormat = "m/d/yyyy"
    Range("L1") = Range("L1") + 1
    Cells(i, "O") = Range("K1")
  Next i
  Cells(j, 4).Resize(1, 4).Sort key1:=Cells(j, 4), order1:=xlAscending, Header:=xlNo, Orientation:=xlSortRows
Next j
Columns("J:O").ClearContents
'MsgBox Timer - t
Application.Calculation = mycalculation
End Sub

Good to hear you are better now,

As for the code - you can just use it as is - the part " To observe it better ... " is intended just to show you how the process goes on. As it works (hopefully) acceptably - you can treat it as a black box and not look inside at the moment :-)