Macro for Random Time Generator with values greater/lesser than previous cells

[KCoble1]

Hello all,

New to this forum and I haven't done Macros before. What I have done with excel is self-taught and therefore I would really appreciate your expertise.

I am trying to build a random time generator that will give me 6 times a day.
3 times between 0600 and 1800, and 3 times between 1800 and 0600. I also need the values to be at least an hour apart. I don't need them generated in order but I need them to all be at least an hour apart and fall within those time frames.

I currently have a table built with formulas, but am running into the problem of it spitting out random times that happen to be within minutes of each other. As an example, the table appears as below

11-Aug Days 14:20 10:26 08:49

Swings 22:29 02:36 02:28​

12-Aug Days 13:00 08:23 13:14

Swings 05:37 18:08 00:40​

13-Aug Days 13:16 06:08 09:11

Swings 18:44 01:00 23:45​

14-Aug Days 14:51 08:14 11:19

Swings 03:35 03:56 05:58​

The formulas that I'm using for the table are as follows:
For 0600-1800 =TEXT(RAND()*(18-6)/24+6/24, "HH:MM")
For 1800-0600 =TEXT(RAND()*(30-18)/24+18/24, "HH:MM")

I haven't discovered a method to produce this otherwise and I have a feeling that I need to use a macro that is programmed specifically for this purpose, which I have little to no idea how to build. The product was presented to my supervisor as is, but he wants to make it less work to use. If any of you can steer me in the right direction or show me what I need to be aiming for, I would greatly appreciate it.

 

[shknbk2]

How about restricting the times in each cell to make sure it chooses a smaller time? For example, for the 0600-1800 block, one chooses times between 0600-0900, the next 1000-1300, and the last 1400-1800. This would force them to be at least an hour apart, though it would also make them be in order even if you don't need it.

=TEXT(RAND()*(9-6)/24+6/24, "HH:MM")
=TEXT(RAND()*(13-10)/24+10/24, "HH:MM")
=TEXT(RAND()*(18-14)/24+14/24, "HH:MM")

Does it matter if the times are separated like this, or do you also need a random time generation where they are all later than 1200, for example?

 

[pgc01]

Hi

Another option

In A1: =RAND()

In B1: ="6:0"+SMALL($A$1:$A$3,ROWS($B$1:B1))*("16:0"-"6:0")+((ROWS($B$1:B1)-1)&":0")

Copy A:B down till row 3

Format B1:B3 as time

 

[KCoble1]

They are for random security checks, so for legality reasons we are trying to limit the restrictions on when the times are generated.

For example, it's perfectly fine if the checks are at 0817, 0917, and 1017. But having them overlapping such as 0817, 0843, 0857 won't work for our purposes because we have to have three separate checks per 12 hour shift.

We have discussed breaking them up into smaller groups and may do so for the time being, but are trying to figure out if there is a way to build a macro that will allow us to have the complete randomized times without having to use human discrepancy to sort out usable and non-usable outputs from the formula, or restrict the formula so that certain times are being excluded.