Add up random time in a specific time range ?

[mizzoh]

Hello everyone,

I have a cell A1 where I generate a random Date+time which is between the time 8:00 - 18:00 :

=RANDBETWEEN(DATE(2019,1,1),DATE(2022,12,31))+RANDBETWEEN(TIME(8,0,0)*10000;TIME(18,0,0)*10000)/10000

Let's say it results in : 29.05.2022 11:52:05

After that, in cell A3 I would like to add up a few hours randomly:

A2+RANDBETWEEN(1,60)/24

It works fine, but it is not in the time frame of 08:00 - 18:00
How am I able to achieve this?

Best regards

 

[Fluff]

Hi & welcome to MrExcel.
How about

=A2+RANDARRAY(1,,TIME(8,0,0),TIME(18,0,0))

 

[mizzoh]

Hi, thank you for the fast reply.
I've tried it but for some reason it still gives me values between 00:00 and 23:59.

 

[Fluff]

If you start with 29.05.2022 11:52:05 and add 9 hours you will get 29.05.2022 20:52:05 which is outside your criteria.

 

[mizzoh]

If you start with 29.05.2022 11:52:05 and add 9 hours you will get 29.05.2022 20:52:05 which is outside your criteria.

Yes, that is correct. This is the result for the first cell. For the second cell I'm searching for a way to make sure Excel only adds up a few hours or minutes if the result will be between 8:00 - 18:00.

A2 from first forumla:
29.05.2022 11:52:05

A3 from second formula:
it should result (e.g.) 29.05.2022 14:55:09 but not something like 29.05.2022 21:43:08

 

[Fluff]

Ok, how about

=A2+RANDARRAY(1,,0,TIME(18,0,0)-MOD(A2,1))

 

[mizzoh]

Ok, how about

=A2+RANDARRAY(1,,0,TIME(18,0,0)-MOD(A2,1))

This works fine. Thanks !
May I ask you if it's possible that I add up a few hours which will be randomly in the next day or even the day after next?

so for instance it would look like this:

A2 from first forumla:
29.05.2022 11:52:05

A3 from your second formula:
it should result (e.g.) 29.05.2022 14:55:09

A4:
30.05.2022 10:11


So basically, that it would add a random amount of minutes between 1 and 1440 (1 day) but would also have a look on the range 8:00 - 18:00


Big thank you so far! I've learned a lot!

 

[Fluff]

How about

=A2+RANDARRAY(1,,0,TIME(18,0,0)-MOD(A2,1))+RANDBETWEEN(1,10)

 

[mizzoh]

Many thanks. You are truly a MVP !

 

[Fluff]

Glad to help & thanks for the feedback.