Random Date Generation

Could someone please help me generate a list of 15 random dates between
1/1/05 and 10/31/05 without any duplicates? Thanks in advance for all your
help.

Frustrated in Tennessee

In column A list all the dates between 1/1/05 and 10/31/05
In cell B1 enter:
=RAND() and copy down
Sort both columns by column B. This will randomize the data in column A.

Just copy out the first 15 items in column A

To get a different 15, pump F9 and resort.
--
Gary's Student


"TNMAN" wrote:

> Could someone please help me generate a list of 15 random dates between
> 1/1/05 and 10/31/05 without any duplicates? Thanks in advance for all your
> help.
>
> Frustrated in Tennessee
>
>
>

Hello,

Take my UDF UniqRandInt() from www.sulprobil.com, select 15 cells in
your worksheet, enter
=UniqRandInt(DATE(2005,10,31)-DATE(2005,1,1)+1)+DATE(2005,1,1)-1
as array formula (with CTRL + SHIFT + ENTER), format cells as date.

HTH,
Bernd

When I try to access your website I time out. Thanks for trying.

<[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> Take my UDF UniqRandInt() from www.sulprobil.com, select 15 cells in
> your worksheet, enter
> =UniqRandInt(DATE(2005,10,31)-DATE(2005,1,1)+1)+DATE(2005,1,1)-1
> as array formula (with CTRL + SHIFT + ENTER), format cells as date.
>
> HTH,
> Bernd
>

Hello,

Time out? Never happened to me.

Anyway, the function is:

Option Explicit

'If lRange >> n then set LATE_INITIALISATION to true
'For example if lRange=1000000 and if 1000 cells are selected (n=1000)
#Const LATE_INITIALISATION = False
'If random integers may occur more than once, allow repetitions
#Const ALLOW_REPETITION = True

Public Function UniqRandInt(ByVal lRange As Long, _
Optional ByVal lMaxOccurence As Long = 1) As Variant

'Returns n unique (=non-repeating) random ints within 1..lRange,
'lRange >= n if n cells in a worksheet have been selected and the
'function has been entered as array formula (CTRL+SHIFT+ENTER).
'Set lMaxOccurences > 1 if random integers may occur more than once.

'Algorithm by: sulprobil http://Reverse("moc.liborplus.www")

Dim vA As Variant
Dim vR As Variant
Dim i As Long
Dim lr As Long
Dim lRow As Long
Dim lCol As Long

Application.Volatile

If TypeName(Application.Caller) <> "Range" Then
UniqRandInt = CVErr(xlErrRef)
Exit Function
End If

#If ALLOW_REPETITION Then
lRange = lRange * lMaxOccurence
If lMaxOccurence < 1 Then
UniqRandInt = CVErr(xlErrNum)
Exit Function
End If
#Else
If lMaxOccurence <> 1 Then
UniqRandInt = CVErr(xlErrNum)
Exit Function
End If
#End If

If Application.Caller.Count > lRange Then
UniqRandInt = CVErr(xlErrValue)
Exit Function
End If

ReDim vR(1 To Application.Caller.Rows.Count, _
1 To Application.Caller.Columns.Count)

ReDim vA(1 To lRange)
#If Not LATE_INITIALISATION Then
For i = 1 To lRange
#If ALLOW_REPETITION Then
vA(i) = Int((i - 1) / lMaxOccurence) + 1
#Else
vA(i) = i
#End If
Next i
#End If

i = 1
For lRow = 1 To UBound(vR, 1)
For lCol = 1 To UBound(vR, 2)
lr = Int(((lRange - i + 1) * Rnd) + 1)
#If LATE_INITIALISATION Then
If vA(lr) = 0 Then 'Late initialisation
#If ALLOW_REPETITION Then
vR(lRow, lCol) = Int((lr - 1) / lMaxOccurence) + 1
#Else
vR(lRow, lCol) = lr
#End If
Else
#End If
vR(lRow, lCol) = vA(lr)
#If LATE_INITIALISATION Then
End If
If vA(lRange - i + 1) = 0 Then 'Late initialisation
#If ALLOW_REPETITION Then
vA(lr) = Int((lRange - i + 1 - 1) / lMaxOccurence) +
1
#Else
vA(lr) = lRange - i + 1
#End If
Else
#End If
vA(lr) = vA(lRange - i + 1)
#If LATE_INITIALISATION Then
End If
#End If
i = i + 1
Next lCol
Next lRow

UniqRandInt = vR

End Function

HTH, Bernd

Another quick way to set it up

Put the start date in A1: 1/1/05
Put in B1: =RAND()
Select A1:B1, copy down to B304:
(In A304 will be the end date: 10/31/05

Put in C1: =INDEX(A:A,RANK(B1,$B$1:$B$304))
Format C1 as date, copy down to C15, which will
> ... generate a list of 15 random dates between
> 1/1/05 and 10/31/05 without any duplicates

Press F9 to re-generate afresh
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"TNMAN" <[email protected]> wrote in message
news:#[email protected]...
> Could someone please help me generate a list of 15 random dates between
> 1/1/05 and 10/31/05 without any duplicates? Thanks in advance for all
your
> help.
>
> Frustrated in Tennessee
>
>