"...I need a formula or method..."

A VBA method follows.
Paste the following code in a standard module.
Add a button to a worksheet and assign the macro "Every5Weeks" to it.

Enter week numbers from 1 to 52 in a column.
Select the cell next to the current week number (or any week number) and click the button.

'code starts....
Sub Every5Weeks()
'Jim Cone - Portland, Oregon USA - July 2009
'Adds a random day of the week to the active cell.
Dim rng As Excel.Range
Dim newRng As Excel.Range
Dim vDays As Variant
Dim strDay As String
Dim lngIndex As Long
Dim DaysOffset As Long

Set rng = ActiveCell
If rng.Column < 2 Then
MsgBox "Use any column except column A. ", _
vbExclamation, "Every Five Weeks"
Exit Sub
ElseIf Application.CountA(rng.Resize(51, 1).Offset(1, 0)) 0 Then
MsgBox "Clear the cells below " & rng.Address(False, False) & " ", _
vbExclamation, "Every Five Weeks"
Exit Sub
ElseIf Not IsNumeric(rng(1, 0).Value) Or LenB(rng(1, 0).Value) = 0 Then
rng.Value = "Week numbers required in column " & rng.Column - 1
Exit Sub
ElseIf rng(1, 0).Value 52 Or rng(1, 0).Value < 1 Then
rng.Value = "Invalid week number in column"
Exit Sub
End If


vDays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
DaysOffset = rng.Offset(0, -1).Value Mod 5
If DaysOffset < 0 Then
Set newRng = rng.Offset(-DaysOffset, 0).Resize(DaysOffset, 1)
Else
lngIndex = Int((4 - 0 + 1) * Rnd + 0)
strDay = vDays(lngIndex)
rng.Value = strDay
rng.Font.Bold = True
rng(2, 1).Select
Exit Sub
End If

Do
' Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
lngIndex = Int((4 - 0 + 1) * Rnd + 0)
strDay = vDays(lngIndex)
Loop Until IsError(Application.Match(strDay, newRng, 0))

rng.Value = strDay
rng.Font.Bold = False
rng(2, 1).Select
End Sub
'....code ends

'--
Jim Cone
Portland, Oregon USA




"Brad"
wrote in message
Hi
We have a system in New Zealand where we need to randomly select one day a
week over one year, simple in itself. However we are to incude all the days
of the week over a 5 week period e.g if we choose Tuesday this week the other
four days need to be randomly selected the following week, three days need
the following week etc restarting five days again at week six.
I need a formular or method of excluding previously selected numbers but
giving all remaining numbers an equal chance of selection.
This needs to be set out on a spread sheet for the entire year
Would really appreciate some advice
Thanks
Regards Brad