Random Date and Time

[Bill Jones]

I am trying to generate a random Date and Time in excel but I need to remove the

volatility out of the Randbetween function. The formula I an currently using is as follows:

=RANDBETWEEN(--"2019-03-17",--"2019-04-29")+RAND()*("0:59"-"17:00")+"1:00"

But by using this function the generated Date and Time in volatile and I need it to stay constant once the Date and Time are generated of that cell.

I am not good an programming Macro's so I have not gone that route but I assume that is the only way to accomplish this task.

If anyone can help it would be appreciated.<strike></strike>
​

 

[Scott T]

If you do not want to use VBA you would need to copy it and past as values to remove the formula and only leave the value.

 

[Scott T]

If you are OK with VBA this should work

Sub randdateandtime()
r = Int((DateValue("4 / 29 / 19") - DateValue("3 / 17 / 19") + 1) * Rnd + DateValue("3 / 17 / 19")) + Rnd * (TimeValue("0:59") - TimeValue("17:00")) + TimeValue("1:00")
Range("A7") = r
End Sub