The knowledge of how to generate random data in Excel is a useful tool to have. Whether it is to test functions, formulas or a spread sheet solution, having access to or generating a lot of data to work with to really give Excel a good workout.I generate a lot of, or find sources of free data to use as dummy data, but it does not always include date data or date fields. So here is how to generate some random dates in Excel.To generate random data we the RANDBETWEEN function. The syntax of this formula is=RANDBETWEEN(bottom,top)Parameters or ArgumentsbottomtopThe smallest integer value that the function will return.The largest integer value that the function will return.Note- if the bottom is greater than the top value then Excel will return the #NUM! error.So back to our formula combining… all we need to do is add the RANDBETWEEN and DATE functions together to get random outputs. The syntax of this formula is -RANDBETWEEN(DATE(startdate),DATE(enddate))The Start date and End date need to be in the format Year, Month, Day.Let’s work through an example. In the example we are using, I want to generate some random dates between 01 January 2014 and 01 January 2015.The formula will look like this=RANDBETWEEN(DATE(2014,01,01)DATE(2015,01,01))Once you hit enter Excel will display a 5 digit number, Excels understanding of dates. Excel stores dates and times as a number representing the number of days since 1900-Jan-0. We just need to get Excel to display the date we can easily understand it.To convert the date to Short Date format-

Home Tab

Number Group

Select the date format needed from the drop down box. In this case I chose short date- it could be any other available option
Great, so we have one random date. To generate more random dates between 01 January 2014 and 01 January 2015 just drag the formula as you would normally to generate dates.Remember that these random dates will recalculate – and different ones will be displayed so if you want to make the random dates static to use in your data analysis then copy and paste the values.

CTRL+A to select th data set

Home Tab

Copy

Home Tab

Paste Special – Values
This will give you a permanent set to work with.