how to generate random dates list within a given range in google sheets

Question

I need to crate a list of 60 random dates between 01/01/1949 to 01/01/2004.

Steps I took:

2.used this formula in cell: =RANDBETWEEN(1/1949,1/1/2004) and got #NUM! error:

What am I missing here?

a few may but not systematically as it needs to simulate birth dates. so i guess among 60 individuals a few may share birth date — danb2000, Jul 23 '21 at 05:27

score 1 · Answer 1 · answered Jul 21 '21 at 14:35

1

Another option generating array:

=ArrayFormula(DATE(1949,1,1)+RANDARRAY(60,1)*DATEDIF(DATE(1949,1,1),DATE(2004,1,1),"D"))

answered Jul 21 '21 at 14:35

basic

score 0 · Answer 2 · answered Jul 21 '21 at 14:33

0

It appears that the values passed to RANDBETWEEN should be integers.

You can use this expression to generate the required random dates:

=date(1949,1,1)+randbetween(0,date(2004,1,1)-date(1949,1,1))

answered Jul 21 '21 at 14:33

raghu

score 0 · Answer 3 · answered Jul 21 '21 at 14:41

There are 20088 days between your date range:

=datedif(datevalue("1/1/1949"),datevalue("1/1/2004"),"D")

or =datevalue("1/1/2004")-datevalue("1/1/1949")

Therefore try this to get a sequential list of random dates:

=arrayformula(query(datevalue("1/1/1949")+int(RANDARRAY(60,1)*20088),"order by Col1",0))

score 0 · Answer 4 · answered Jul 28 '21 at 21:32

0

OK - FYI, It tried out this syntax and it works like a charm:

=RANDBETWEEN(DATE(1949, 1, 1),(DATE(2004,1,1)))

Many thanks for all those who suggested their answers above

answered Jul 28 '21 at 21:32

danb2000

4 Answers4