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,RANDBETWEEN(45,180),0))

[testingcode]

Still learning. Also didn't see the formula part

[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.