Randon date generation prior to 1900s

[Biking Loki]

I am trying to figure out a way to generate a random date that will also consider dates prior to 1900. I've fiddle around with the RANDBETWEEN function but that seemed to only generate dates at the upper end of the range. Also, I could not get it to work for pre-1900 dates.

Does anyone have any suggestions for a formula or method to make this possible? Is it even possible given Excel's issues with dates prior to the 1900s?

Thank you in advance.

 

[Oaktree]

Building on this idea: Deal With Dates Before 1900 you could first randomly generate a date 1,000 years after you needed first...

 

[Biking Loki]

Thanks for that article link. I actually found that prior to making this post. I could get it to work though. Hence my posting this thread.

 

[Biking Loki]

I have this...

=DATE(RANDBETWEEN(1900,3500),RANDBETWEEN(1,12),RANDBETWEEN(1,31))

But I would like to make it handle dates prior to 1/1/1900.

 

[Biking Loki]

I have this...

=DATE(RANDBETWEEN(1900,3500),RANDBETWEEN(1,12),RANDBETWEEN(1,31))

But I would like to make it handle dates prior to 1/1/1900.

I just noticed a weird querk! Whenever it hit enter for another cell on the sheet, it recalculates any cell with that formula. Is there a way to make it only execute the formula for each cell one time?

 

[Oaktree]

Say you want to generate a random date between 1800 and 2021. The way you'd use this approach is to generate a date between 2800 and 3021, then subtract 1,000 years from it.

Or, more explicitly, put 1/1/2800 in A1 and 12/31/3021 in A2. Then, C1 =RANDBETWEEN($A$1,$A$2), D1 =MONTH(C1), E1 =DAY(C1), F1 =YEAR(C1)-1000, and G1 =D1&"/"&E1&"/"&F1 (or swap D1 and E1 if you use d/m/yyyy formatting). Copy C1:G1 down as many times as you want.

Column C will pull new random numbers whenever you press F9. If you don't want that to happen, hardcode (copy & paste values) the values in column C.

 

[Biking Loki]

Thanks for the thorough response.

I entered 1/1/2800 in A1 and 12/31/3021 in A2. However, when I enter =RANDBETWEEN($A$1,$A$2) in C1 I get a #NUM! error. I entered the rest of the info above ins D1, E1, F1, and G1 and also got #NUM! errors. But I think once C1 is fixed, they will resolve themselves.

I thought it was maybe a formatting issue with C1 but I can't seem to make the #NUM! go away.

Did I mess something up?

 

[Oaktree]

A #NUM! error with RANDBETWEEN usually means you have the larger value listed first. Maybe you have a typo and entered the second one as 12/31/2021 (instead of 12/31/3021)?

 

[Biking Loki]

I'm a fool! I figured out my problem. I had the dates in A1 and B1, not A1 and A2. Sometimes it's the smallest things that get you. Now, the formula works like a charm.

However, I am still chasing the problem of it recalculating the formula (random date) every time I enter something in another cell.

Once a random date is generated, I want to save the just created date(s) while having the ability to generate new ones.

Any thoughts on that part of it?

 

[Oaktree]

Glad it was simple. You may have missed the part I mentioned above that the way to keep the numbers after they're generated is to just hardcode them (i.e. copy & paste the values).

If you still want to generate new ones, the simplest way is to keep two columns: one for live formulas to generate random numbers and another where you hardcode (copy/paste the values from the formulas) as the ones you're using (i.e. the other formulas would reference the hardcoded column, not the one that recalculates).