Tag Archives: using randbetween

Generating random date values using RANDBETWEEN

Hello friends,

All dashboards need one thing to work – data. However, while building dashboards we very often do not have access to the original data and this compels us to create random data sets. While most of us know how to create numeric data sets (by using the RAND function), I often get a lot of queries on generating random date values. So, I thought to post something on my approach of generating them.

It used to be a bit tricky in excel 2003, especially if one wanted dates to be within a range – which is mostly the case as dashboards usually have data for the relevant 5-6 years and it would not make a lot of sense to get dates that are 100 years old. However, with the Excel 2007 & 2010 RANDBETWEEN function it has become really easy. How?

Excel dates are nothing but numbers – Don’t trust me? Test it yourself – write a date in a cell in Excel, now go to the ‘number’ sub-tab on the home ribbon and change the format to number, surprised!! Well basically this is how Excel stores dates and hence it makes it fairly easy to compared dates.

Now, let’s come back to the problem. First of all, let’s explore the format of the RANDBETWEEN function:

RANDBETWEEN ( bottom, top)

This function requires two inputs - ‘bottom’ and ‘top’. Both the inputs require numeric integer values. Continue reading