Adding random hours to Date

[Firasath]

Hi,

I'm using Vista OS and Excel 2007.

How can I add random hours to a date... I have a list of something around 7000 dates and I would like to add random time in between say 3 minutes to 8 hours, to all the dates... I have tried using RANDBETWEEN function, but I couldn't succeed. The date format I'm using is dd/mm/yyyy hh:mm AM/PM. Is there any good way to achieve it?

 

[barry houdini]

In Excel a day is 1 so because RAND() generates a random value >= 0 and < 1 that will effectively add a random time, so if you have a date in A2 you could use this formula in B2

=A2+RAND()

If it must be betweem 3 minutes and 8 hours then, because there are 1440 minutes in a day this will give a random time between those but always on a whole minute

=A2+RANDBETWEEN(3,480)/1440

 

[Weaver]

Re: Adding random hours to bunch of Dates

try this

=A1+RANDBETWEEN(3,8*60)/24/60

Assuming your date value is in A1

 

[Firasath]

In Excel a day is 1 so because RAND() generates a random value >= 0 and < 1 that will effectively add a random time, so if you have a date in A2 you could use this formula in B2

=A2+RAND()

If it must be betweem 3 minutes and 8 hours then, because there are 1440 minutes in a day this will give a random time between those but always on a whole minute

=A2+RANDBETWEEN(3,480)/1440

Thanks alot, it helped me a lot. I got one more formula, which also worked for me. It is =A1+ROUND(RANDBETWEEN(0,800)/2*400,5). Both of them worked great for me. Thanks a lot.

 

[barry houdini]

=A1+ROUND(RANDBETWEEN(0,800)/2*400,5)

I'm not really sure what that formula is supposed to do, the ROUND function is redundant so it does the equivalent of

=A1+RANDBETWEEN(0,800)*200

which will add multiples of 200 days to the date in A1

 

[Firasath]

Thanks alot, it helped me a lot. I got one more formula, which also worked for me. It is =A1+ROUND(RANDBETWEEN(0,800)/2*400,5). Both of them worked great for me. Thanks a lot.

It is not /2*400 it is /2400... I got it from one of the online Users and it worked for me. I got the following response from that User.

"The RANSBETWEEN and /2400 gives random numbers between 0 and 8/24 (decimal representing proportion of 1 24 hour day). The ROUND is because occassionally you get a small decimal above 8 hours and I wanted to avoid that".

 

[Weaver]

So why would you be dividing by 2400?