Generate random times

[BC2005]

Hi Everyone,
Wondering if anyone can help me out?
Basically I want to create a random time generator for times that fall between a start and end time when the end time is the following day, I would also like to exclude certain time frames.
An example would be : 8 random times needed to be generated between 17:30 and 01:30(inclusive) but need to exclude times from 19:45-20:00, 22:00-22:30, 00:45-01:00.

I have searched and searched for something but cannot seem to find anything that will work.

Any help would be greatly appreciated.

 

[AlphaFrog]

Sub Random_Times()
' 8 random times needed to be generated between 17:30 and 01:30(inclusive)
'but need to exclude times from 19:45-20:00, 22:00-22:30, 00:45-01:00.
   
'random to the nearest minute
   
    Static IsRandomized As Boolean
    Dim RandTime(1 To 8) As String, rt As Date
   
    If Not IsRandomized Then Randomize: IsRandomized = True    'Seed randomizer once
   
    For i = 1 To 8
        Do
            DoEvents
            rt = Int((480 - 0 + 1) * Rnd + 0) / 1440 'random 0-480 minutes (time span in minutes)
            rt = rt + 1050 / 1440                    'shift random minute to between 17:30 and 01:30
            rt = rt - IIf(rt >= 1, 1, 0)             'subtract day if past 2400
            RandTime(i) = Format(rt, "hh:mm")        'Format time as text for msgbox
            Select Case rt                           'Exclude time ranges
                Case TimeValue("19:45") To TimeValue("20:00")
                Case TimeValue("22:00") To TimeValue("22:30")
                Case TimeValue("00:45") To TimeValue("01:00")
                Case Else: Exit Do
            End Select
        Loop
    Next
   
    MsgBox Join(RandTime, vbLf), , "Eight Random Times"
   
End Sub

 

[6StringJazzer]

Here is the strategy I took.

Create a table based on the allowable time intervals you gave.
Determine the duration of each allowable interval. (The total allowable time is 7 hours.)
Create a parallel table that covers 00:00 to 7:00 using the same increments as your intervals.
Generate a random time. Look it up in the parallel table. On that row will be the corresponding allowable interval.
Calculate the time from the beginning of the parallel interval, and then add it to the beginning of the allowable interval to calculate the actual time.

You have to have one cell with the random number and another cell with the formula, because the random number is used in three places in the formula. If you use RAND In the formula you will get three different random numbers.

Here is what it looks like. Here is a link.

BC2005=random times.xlsx

Shared with Dropbox

www.dropbox.com

BC2005=random times.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Random Time	Adjusted Time		Duration	Span		Start	End	Day offset	End
2	0:18	17:48		2:15	0:00	2:15	17:30	19:45	0	19:45
3				2:00	2:15	4:15	20:00	22:00	0	22:00
4				2:15	4:15	6:30	22:30	0:45	1	0:45
5				0:30	6:30	7:00	1:00	1:30	1	1:30
Sheet2

Cell Formulas
Range	Formula
A2	A2	=RAND()*$F$5
B2	B2	=A2-VLOOKUP(A2,$E$2:$G$5,1,TRUE)+VLOOKUP(A2,$E$2:$G$5,3,TRUE)
D2:D5	D2	=MOD(J2-G2,1)
F2:F5	F2	=E2+D2
E3:E5	E3	=F2
J2:J5	J2	=H2+I2

 

[Sulprobil]

10,000 random times - please see image:

Copy A5:C5 down to row 10,001:

MrExcel_Generate random times.xlsm
	A	B	C	D	E
1		Time	Scatter	Lookup Table
2	02:53:18	20:38:18	0,32590419	00:00	17:30
3	06:51:49	01:21:49	0,55335911	02:15	17:45
4	01:33:11	19:03:11	0,15429642	04:15	18:15
5	02:18:47	20:03:47	0,01234421	06:30	18:30
Sulprobil

Cell Formulas
Range	Formula
A2:A5	A2	=RAND()*TIME(7,0,0)
B2:B5	B2	=MOD(A2+VLOOKUP(A2,$D$2:$E$5,2,TRUE),1)
C2:C5	C2	=RAND()
E2	E2	=TIME(17,30,0)
D3	D3	=TIME(19,45,0)-TIME(17,30,0)
E3	E3	=E2+TIME(20,0,0)-TIME(19,45,0)
D4	D4	=D3+TIME(22,0,0)-TIME(20,0,0)
E4	E4	=E3+TIME(22,30,0)-TIME(22,0,0)
D5	D5	=D4+TIME(23,59,0)+ TIME(0,46,0)-TIME(22,30,0)
E5	E5	=E4+TIME(1,0,0)-TIME(0,45,0)

 

[Sulprobil]

Here is the strategy I took.

Create a table based on the allowable time intervals you gave.
Determine the duration of each allowable interval. (The total allowable time is 7 hours.)
Create a parallel table that covers 00:00 to 7:00 using the same increments as your intervals.
Generate a random time. Look it up in the parallel table. On that row will be the corresponding allowable interval.
Calculate the time from the beginning of the parallel interval, and then add it to the beginning of the allowable interval to calculate the actual time.

You have to have one cell with the random number and another cell with the formula, because the random number is used in three places in the formula. If you use RAND In the formula you will get three different random numbers.

Here is what it looks like. Here is a link.

BC2005=random times.xlsx

Shared with Dropbox

www.dropbox.com

BC2005=random times.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Random Time	Adjusted Time		Duration	Span		Start	End	Day offset	End
2	0:18	17:48		2:15	0:00	2:15	17:30	19:45	0	19:45
3				2:00	2:15	4:15	20:00	22:00	0	22:00
4				2:15	4:15	6:30	22:30	0:45	1	0:45
5				0:30	6:30	7:00	1:00	1:30	1	1:30
Sheet2

Cell Formulas
Range	Formula
A2	A2	=RAND()*$F$5
B2	B2	=A2-VLOOKUP(A2,$E$2:$G$5,1,TRUE)+VLOOKUP(A2,$E$2:$G$5,3,TRUE)
D2:D5	D2	=MOD(J2-G2,1)
F2:F5	F2	=E2+D2
E3:E5	E3	=F2
J2:J5	J2	=H2+I2

Hello,

If I am not mistaken, your approach does not generate any time between 0:00 and 1:00:

MrExcel_Generate random times.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Random Time	Time	6StringJazzer	Duration	Span		Start	End	Day offset	End
2	04:35:09	22:50:09	0,58697781	02:15	00:00	02:15	17:30	19:45	0	19:45
3	05:17:39	23:32:39	0,03163789	02:00	02:15	04:15	20:00	22:00	0	22:00
4	05:22:44	23:37:44	0,87674664	02:15	04:15	06:30	22:30	00:45	1	00:45
5	04:44:43	22:59:43	0,57752589	00:30	06:30	07:00	01:00	01:30	1	01:30
6StringJazzer

Cell Formulas
Range	Formula
A2:A5	A2	=RAND()*$F$5
B2:B5	B2	=A2-VLOOKUP(A2,$E$2:$G$5,1,TRUE)+VLOOKUP(A2,$E$2:$G$5,3,TRUE)
C2:C5	C2	=RAND()
D2:D5	D2	=MOD(J2-G2,1)
F2:F5	F2	=E2+D2
E3:E5	E3	=E2+D2
J2:J5	J2	=H2+I2