0

I want to randomly generate dates (within a four-year period) for 32 different records/rows in excel.

Additionally, I want 75% of those dates to be during one year in particular (within that four year period). Is there anyway to use the =randombetween funtion in excel to acheive this task?

Thanks for your help.

  • Do you need a hard 75% of the those dates to be within that one year, or do you need a 75% probability that dates are within one year? The first means that you'd have exactly 24 dates that are in the one year, while the later means that you'll have around that number in that one year, but outlier runs could mean that you have no dates or all dates in that year (and everything in between). – ernie Jun 6 '14 at 16:35
  • Joe, it would be very helpful if you showed some effort by including what you've already attempted and where you got stuck. Please include the formula you're working with. – CharlieRB Jun 6 '14 at 17:08
3

You could use a combination of rand and randbetween to get the answer you want

=IF(RAND()<0.75,RANDBETWEEN(Q2,Q3),RANDBETWEEN(R2,R3))

The function first generates a random number between 0,1. If the number is less then 0.75 (75% of the time) it uses the first randbetween function, if its higher it uses the other one.

The randbetween takes your start and end dates and returns a random date in that range.

Note the way the function is setup assumes the date ranges are mutually exclusive and that your high probability dates are at the start (or end) of the timeline. If its in the middle you would need to adjust to take a third randbetween function.

  • This is a good answer. A suggestion to improve it; for those who might not be proficient with Excel, explain a little better how to implement it. – CharlieRB Jun 6 '14 at 17:14
  • @gtwebb This worked flawlessly as described! Thanks for your help! – Joe Jun 7 '14 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.