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.
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
- 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.
- Home Tab- Number Group-Select the date format needed from the drop-down box. In this case, I chose short date
- To generate more random dates just drag the cursor as below