In this post we will see how we could generate random dates between two given dates in excel.
Creating dates manually is a time taking task, you need to provide the arguments in exact format that you want your date to take.
In excel we have a function called RANDBETWEEN(Start,END) which generates random number between start and end, start and end are integers that we provide to this function.
Example RANDBETWEEN(0,10) will give us any number between 0 and 10.
We will use RANDBETWEEN function and DATE function in excel to generate random dates.
We could populate a range, a column, a row or any set of cells with random dates.
Below you could see the column A is populated with randomly defined dates between Jan 1st 2016 and Dec 1st 2017.
Step 1
Enter the formula =RANDBETWEEN(DATE(2016,1,1),DATE(2017,12,1)) in cell A1 as shown below.
DATE function takes three argument DATE(YEAR,MONTH,DAY) and generates date based on the inputs provided.
So in this case as we wanted the random dates from 2016 and 2017 hence we have provided the arguments as 2016,1,1 in start and 2017,12,1 in end.
If we wanted the random dates between March 3rd 2018 and June 2nd 2020), we would write the formula =RANDBETWEEN(DATE(2018,3,3),DATE(2020,6,2))
Step 2
Hit enter and copy down the formula till the row which you want random dates to be generated.
Step 3
Since excel stores the date as number and hence we are seeing just numbers, we will have to format these numbers to Date format as shown below, select the entire range and go to formatting drop down in Home tab and select Short Date as format to change the format of randomly generated dates.
Step 4
As you could see we have generated random dates in a column in excel in a simple way.
One thing to notice here that since we have not pasted the formula as values, on every event the RANDBETWEEN formula will generate random values and your random dates will change at every click or event, so better to paste the formula as values.
Hope this helped.
