Tuesday, July 15, 2014

Random date generation

Today I needed to generate a large (bulk) set of random dates for a project at work.  Probably a million ways to do this, but I turned to Excel. I've used the RAND function before and was expecting to use something ugly like this for month:
=INT(RAND()*12)+1

But as I'm always looking for new cool ways to be more efficient, I stumbled upon this cool function in XLS. Not sure when it was introduced, but it is cool. Simply use RANDBETWEEN in combination with the DATE function.  To generate a random date between 2 dates, simply use:
=RANDBETWEEN(DATE(2014,1,1),DATE(2014,7,15))

Note that new random data is calculated every time calculations are performed, like when you add a new formula. RAND works the same way.

Nifty,
Thomas


No comments: