How do you generate a random date between two dates?

I was asked how to generate a set of random dates between two dates. At first glance, it didn’t look like Excel had a function that would easily allow that. However, it turns out that using the RANDBETWEEN function is perfectly suited for this.

Sample formula

You will use the DATE function to specify your start and end dates for the range.

Let’s say you want to generate a date between January 1, 2013 and December 31, 2013. The function would look like the following.

=RANDBETWEEN(DATE(2013,1,1),DATE(2013,12,31))

How does this work?

This works because the DATE function returns a number that represents the date value in Excel. Then RANDBETWEEN generates a random number between those two date values (or numbers).

The result will be a number that represents the date in Excel. If you want to see this formatted as a date instead of a number just change the number format from General to one of the date formats such as Short Date or Long Date.

There you go.