If you need to generate random dates in Excel, this post will show you how to do it using a formula and a VBA code. While there is a built-in formula for generating random numbers in Excel, there is no direct formula for generating random dates.

However, you can create a user-defined function in VBA that will generate a random date between two given dates. Or you can also use a combination with RANDBEWEEN and DATE functions to generate random date in Excel.

1. Generate Random Date in Excel using Formula

Step1: 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.

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

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

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

Step5: 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.

Step6: 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.

Comment:

a. 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.

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

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

b.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.

2. Generate Random Date in Excel using User Defined Function with VBA Code

You can generate a random date in excel using a User defined function with VBA code. Just do the following steps:

Step1: Press ALT + F11 to open the Visual Basic Editor (VBE).

Step2: In the VBE, go to “Insert” > “Module” to create a new module.

Step3: Copy and paste the following VBA code into the new module. Save the module and close the VBE.

Function RandomDate_ExcelHow(startDate As Date, endDate As Date) As Date
    Randomize
    RandomDate_ExcelHow = Int((endDate - startDate + 1) * Rnd + startDate)
End Function

Step4: Go back to your Excel workbook and select the cell where you want to use the user-defined function. Enter the following formula in a blank cell:

=RandomDate(DATE(2022,1,1), DATE(2022,12,31))

Where “DATE(2022,1,1)” and ” DATE(2022,12,31)” are the dates between which you want to generate a random date.

Step5: Press ENTER to generate the random date.

Note: those two arguments in the formula should be entered as Excel date values (e.g., DATE(2022,1,1)). If you enter them as text values, the function may not work correctly.

3. Video: Generate Random Date in Excel

This video will demonstrate how to use VBA code and a custom formula to generate random dates in Excel.

4. 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)…