Random Time Between Fixed Lower And Upper Limits

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

Quote:

---

Originally Posted by 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

---

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:

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"

---

testingcode, you could limit the lines to this

Code:

---

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

Code:

---

        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))

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

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

Code:

---


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.

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

Code:

---

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

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

Quote:

---

Originally Posted by testingcode

Code:

---

=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

Quote:

---

Originally Posted by papabill

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

I understand now, thanks.