Random Time Between Fixed Lower And Upper Limits

[papabill]

I've been able to create random numbers, but I cannot create random times. For example, I want to create a schedule of training and use random times to call the group together (between 6:30 and 9:00, or between 11:45 and 2:00). Is there any formula to do this?

Thanks

[testingcode]

I've been able to create random numbers, but I cannot create random times. For example, I want to create a schedule of training and use random times to call the group together (between 6:30 and 9:00, or between 11:45 and 2:00). Is there any formula to do this?
Thanks

Have you tried creating random numbers and adding them to your start times with TimeSerial() ?


Edit: Tested it, and it was better to use just one random number and figure the minutes between your times. 6:30 to 9:00 would be 150minutes.

Edited code:


Dim randomminute As Integer
Dim StartTime1 As String

StartTime1 = "6:30 AM"

randomminute = Int((150 - 0 + 1) * Rnd + 0)
ActiveCell.Value = StartTime1
ActiveCell.Value = ActiveCell.Value + TimeSerial(0, randomminute, 0)
ActiveCell.NumberFormat = "hh:mm am/pm"

[Excel Fox]

testingcode, you could limit the lines to this



ActiveCell.Value = TimeValue("06:30:00") + TimeSerial(0, Int((150 - 0 + 1) * Rnd + 0), 0)


However, OP wanted to include two time ranges.

Here's one way to do that



Randomize
If Rnd > 0.5 Then
ActiveCell.Value = (TimeValue("09:00:00") - TimeValue("06:30:00") + 0.0001) * Rnd + TimeValue("06:30:00")
Else
ActiveCell.Value = (TimeValue("14:00:00") - TimeValue("11:45:00") + 0.0001) * Rnd + TimeValue("11:45:00")
End If


Having said that, since the original request was for a formula, this should suffice

=IF(RAND()<0.5,TIME(6,RANDBETWEEN(30,180),0),TIME(11,RANDBETW EEN(45,180),0))

[testingcode]

Still learning. Also didn't see the formula part :sleepy:

[papabill]

That seems to work well, but the products are very random. I still need to control them.

How do I put it so it will give me times between 6 - 9am, 11am - 2pm, 6pm - 8pm, and 11pm - 12:30am



Day Stretches Running warmups Pulls Pushes
Mon 6:30am 11:15am 6:15pm 11:30pm
Tue 8:00am 1:30pm 7:20pm 11:15pm
Wed etc. etc. etc. etc.


Times are changing due to other activities and training.

[testingcode]

Are you wanting 6-9am in the Stretches column, 11am-2pm in the Running warmups column, ect..?

If so, then you can just use the


=TIME(6,RANDBETWEEN(0,180),0)

in the correct column and adjust the bolded numbers to whatever time frame is needed for that column. The example formula above would be for the Streches column.

[papabill]

Thanks to all you for the help. Now I can create a good schedule.

[papabill]

=TIME(6,RANDBETWEEN(0,180),0)



Can you explain something for me? I understand MOST of the formula, but not all. =TIME(6,RANDBETWEEN(0,180),0)

I understand the 6 (6 oclock) and the 180 (180 minutes-three hours past 6 = 9), but what about the 0 in (0,180)?

Thanks

[testingcode]

Can you explain something for me? I understand MOST of the formula, but not all. =TIME(6,RANDBETWEEN(0,180),0)

I understand the 6 (6 oclock) and the 180 (180 minutes-three hours past 6 = 9), but what about the 0 in (0,180)?

Thanks

RANDBETWEEN(0,180) means that it picks a random number between 0 and 180. 0 is your lower bound and 180 is your upper. For example, a RANDBETWEEN(10,180) would never return a 1, 2, or 3, ect..

[papabill]

I understand now, thanks.