So I am trying to make a random date generator that can accept years from 0 to 9999. The problem is that entering a year before January 1, 1900 will not be able to read it. Is there a way that I can make a random date generate before January 1, 1900? Thank you

CodePudding user response：

Use automatic coercion of text strings to dates, instead of the date() function, like this:

=to_date( join("-", randbetween(100, 9999 100), randbetween(1, 12), randbetween(1, 28)) randbetween(0, 3) - 100 * 365 )

The 100 bit is required to avoid the undesirable detection of years like 91 as 1991.

To format the date as "17 Sep 2021", use Format > Number > More formats > More date and time formats.

See this answer for an explanation of how date and time values work in spreadsheets.

CodePudding user response：

Using negative numbers can also turn the numbers into dates =RANDBETWEEN(-1,DATE(1999, 12, 31)) Just figured this out