How to Generate Random Date in Excel

In some special situations we may need to generate some random dates in worksheet. We can enter date manually, but if we want to require a lot of random dates, we will spend a lot of time on manual entry, and this work is very boring. Above all, we need to find out a quick and convenient way to insert random dates in excel, this tutorial will help you to solve this problem by enter formula with RANDBEWEEN and DATE functions.

Generate Random Date in Excel

Step 1: Select one cell, enter the formula =RANDBETWEEN(DATE(2019,1,1),DATE(2019,12,31)). RANDBETWEEN function returns data between bottom and top, DATE function returns a date. This formula will return a random date between 2019,1,1 and 2019,12,31.

Generate Random Date 1

Step 2: Click Enter to get the result. Verify that five digits number 43617 is displayed.

Generate Random Date 2

Step 3: In Home ribbon, click Generate dropdown list in Number group.

Generate Random Date 3

Step 4: Select Short Date or Long Date format depends on your requirement.

Generate Random Date 4

Step 5: Verify that previous number is converted to date format properly. And this date is a random date between start and end dates we entered in formula.

Generate Random Date 5

Step 6: For the purpose generate multiple random dates, we just need to drag fill handle down to create more random dates. You can also copy this formula to other cells to get more random dates.

Generate Random Date 6


1. After step 2, you can also change number to other custom date format in Format Settings as well.

Right click to load menu, select Format Cells.

Generate Random Date 7

Under Number tab, select Date in Category, select date format in Type, for example YYYY-MM-DD.

Generate Random Date 8

After above setting, click OK. Verify that number is changed to date format properly.

Generate Random Date 9

2.This formula returns a date between two entered dates; it can be a weekday or weekend. If you want to only generate weekday exclude weekends, you can enter this formula =WORKDAY(RANDBETWEEN(DATE(2019,1,1),DATE(2019,12,31))-1,1). Then you can find that generated random dates are weekdays.

Related Functions

  • Excel RANDBETWEEN Function
    The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…

You might also like: