Excel tip: generate random dates with RANDBETWEEN and DATE functions in Excel

Knowledge of how to generate random data in Excel is a useful tool. Whether it’s testing functions, formulas, or a spreadsheet solution, accessing or generating a large amount of data to work with to really give Excel a good workout.

I generate many or look for free data sources to use as dummy data, but they don’t always include date data or date fields. So here’s how to generate some random dates in Excel.

To generate random data we use the RANDBETWEEN function. The syntax of this formula is

= RANDBETWEEN (down, up)

Parameters or arguments

background

top

The smallest integer value that the function will return.

The largest integer value that the function will return.

Note: If the lower value is greater than the upper value, Excel will return the #NUM. error.

So back to our combination of formulas … all we have to do is add the RANDBETWEEN and DATE functions together to get random outputs. The syntax of this formula is:

RANDBETWEEN (DATE (start date), DATE (end date))

Start date and end date need tor have the format Year, Month, Day.

Let’s work with an example. In the example we are using, I want to generate some random dates between January 1, 2014 and January 1, 2015.

The formula will look like this

= RANDOM BETWEEN (DATE (01.01.2014) DATE (01.01.2015))

Once you press enter, Excel will display a 5 digit number, excel understanding of dates. Excel stores dates and times as a number that represents the number of days since 1900-Jan-0. We just need Excel to display the date that we can easily understand.

To convert the date to the short date format:

• Home tab

• Group of numbers

• Select the required date format from the drop-down box. In this case, I chose a short date, it could be any other available option.

Great, we have a random date. To generate more random dates between January 1, 2014 and January 1, 2015, simply drag the formula as you normally would to generate dates.

Remember that these random dates will be recalculated and different ones will be displayed, so if you want the random dates to be static for use in your data analysis, please copy and paste the values.

• CTRL + A to select the data set

• Home tab

• Copy

• Home tab

• Paste Special: Values

This will give you a permanent set to work with.