Randomly Generate Dates in Excel

bizfluent article image

John Foxx/Stockbyte/Getty Images

Sometimes when you're working on a school project using Excel, you need to create fake data. If the dummy data you are looking to create involves generating random dates, you can do complete this task by combining two widely used Excel functions: RANDBETWEEN() and DATE().


In Microsoft Excel, the RANDBETWEEN() function returns a random number that is in range, which you define. The DATE() function returns a series of numbers that represent a day of the year. When you combine the RANDBETWEEN() function with the DATE() function, you can turn random numbers into random dates.

How to Use

The RANDBETWEEN() takes two variables: bottom and top. The bottom variable represents the smallest integer RANDBETWEEN will return, while the top variable is the largest.

For example, the formula:


would return a random number between 1 and 100, where 1 is the bottom variable and 100 is the top variable.

If you replace the bottom and top numbers with the DATE() function, in which the function takes the form DATE(year, month, day), then you can generate random dates.

The function would take the general form:


To understand how it would work with real dates, the following function would return random dates between January 1, 2000 and December 31, 2013: