How to generate random data of birth dates using google spreadsheets for a specific years?

Question

I want a formula to generate random data of birth dates for a specific years (Example: 1995 to 2002) and make it Array like this:

Sheet URL: https://docs.google.com/spreadsheets/d/1XHoxD-hNmpUOMVm_u-cz-4ESrabodsrS0fIfaN-n4js/edit

Answer 1

That might not be the best approach but it will get you closer to what you want:

=DATE(RANDBETWEEN(1995,2002),RANDBETWEEN(1,12),RANDBETWEEN(1,31))

There are two issues with this approach:

• you might get a day that does not exist for the particular month. For example, 2/28/2021 exists, but 2/29/2021 does not exist.

• I wasn't able to generate an array but only drag down formulas. When I generate an array, the same random numbers are used and as a result the dates are the same.

For the first issue, you can use isdate to check if the random date returned is correct. For example, 2/29/2021 is a wrong date (I hardcopied that date).

but I guess you can filter out the FALSE cases.

I really hope other people can come up with a better approach.

Answer 2

You could try (as I demonstrated in your sheet):

=ARRAY_CONSTRAIN(SORT(SEQUENCE(DATE(1992,12,31)-DATE(1900,1,1),1,DATE(1900,1,1)),RANDARRAY(DATE(1992,12,31)-DATE(1900,1,1)),1),COUNTA(A2:A),1)

• SEQUENCE(DATE(1992,12,31)-DATE(1900,1,1),1,DATE(1900,1,1)) - Is used to create an array of valid numeric representations of true dates between 1-1-1900 and 31-12-1992.
• SORT(<TheAbove>,RANDARRAY(DATE(1992,12,31)-DATE(1900,1,1)),1) - Is used to sort the array we just created randomly.
• ARRAY_CONSTRAIN(<TheAbove>, COUNTA(A2:A),1) - Is used to only return as many random birth-dates we need according to other data.

Note that this is volatile and will recalculate upon sheet-changes and such. Also note that this is just "slicing" a given array and may fall short when you try to use it on a dataset larger than the given array.