Excel Tip - Learn How To Generate Random Dates With RANDBETWEEN and The DATE Functions - Formula Friday

Hello Excellers, and it is that time of week again. It is time for some more Excel formula fun. Today I want to share with you a great excel tip. I am going to show you how to generate random dates using an Excel formula. Don’t forget to find me on the #formulafriday and follow me on twitter for daily Excel tips.

Why generate random dates in Excel?

That is a good question. I generate random data or look to find sources of data to use a dummy data. Data sets are always useful to have on hand to test Excel solutions that you may be building. A lot of the time you want to generate names and addresses, or Sale data to test your worksheets. But, did you know you can also generate random dates as well to use in these dummy data sets.

What Excel formula do you use to generate random dates?

As with other random data generation, we will use the RANDBWTEEN function, but it does not work alone to generate dates. We will also use and combine it with the DATE function

If you want to read my other blog posts on random data generation you can do so below.

Formula Friday – Generate Random Odd Or Even Numbers With RANDBETWEEN

Formula Friday – Randomly generate words in Excel with INDEX and RANDBETWEEN

Macro Mondays- Excel Generate Random Numbers With A Simple Excel Macro

But for now, let’s get back to dates, where we combine the RANDBETWEEN and DATE functions. I will walk you through an example.

Select the cell you want to generate random data in.
Use the formula as below

=RANDBETWEEN(DATE(Startdate),DATE(Enddate)

The Startdate and Enddate need to be in the format Year, Month, Day

In the example we are using, I want to generate some random dates between 01 January 2014 and 01 January 2015.
Excel will display a 5 digit number, which we just need to convert to a date.