Generate Random Dates In Excel

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.

table showing random dates

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))

formula to create random dates in excel

Step 2

Hit enter and copy down the formula till the row which you want random dates to be generated.




formula showing how to generate random dates in excel

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.

table showing formula driven random 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.

Share The Knowledge

Random Posts

  • Calculate Weighted Average In Excel

    Let’s see how to calculate a weighted average in excel. Weighted average is an average in which each quantity to […]

    Share The Knowledge
  • Excel Deselect Cells/All

    This post demonstrates how to deselect all the cells without changing the active cell in excel The features is available […]

    Share The Knowledge
  • Assign/Change Yes To 1 And No To 0 In Excel

    In this post we will see how we could assign/change the value of “Yes” to 1 and “No” to 0. […]

    Share The Knowledge
  • Insert Page Number In Excel

    Let’s see how to insert the page number in excel. We will see how to insert simple page number and […]

    Share The Knowledge

Leave a Reply