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

KCoble1

New Member
Joined
Aug 13, 2019
Messages
2
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top