Random Date Generator in Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to generate random dates in Excel and Google Sheets.

random date generator

 

Random Date Generator

For various simulations, you may need to generate a random date. To do this in Excel, use the RANDBETWEEN Function or, If you’re working in Excel for Microsoft 365 (or versions of Excel after 2019), you have the option to use the RANDARRAY Function instead.

RANDBETWEEN Function

The RANDBETWEEN Function generates a random number between two defined values.

In this example, we use dates for the RANDBETWEEN limits to return a random date in the year 2020.

=RANDBETWEEN(DATE(2020,12,1),DATE(2020,12,31))

random date generator EX 01

You can use the DATE function to generate your limit dates, or you can refer to date cells instead:

=RANDBETWEEN(B3,C3)

random date generator EX 02

Don’t forget to format your result cell to display the generated number as a date.

Generate Random Workdays

You can also generate random workdays by combining the WORKDAY and RANDBETWEEN functions.

=WORKDAY(RANDBETWEEN(B3,C3)-1,1)

random date generator EX-03

Using the WORKDAY function with the arguments above shifts dates that fall on weekends (or holidays) to the next working day, so the generated random workday can be one or two days later than the end date.

RANDARRAY Function

The RANDARRAY Function is available only in Excel for Microsoft 365 (versions of Excel after 2019). It can generate an array of random numbers with the specified number of rows and columns, minimum and maximum values, and with decimal values (FALSE) or whole numbers only (TRUE). Its syntax is:

randarray

=RANDARRAY(2,3,B3,C3,TRUE)

random date generator EX 04

This example generates a 2-row, 3-column array filled with dates (if cells are formatted accordingly) between the dates specified in the referenced cells. You only need to enter the formula in the first cell and don’t need to use CTRL + SHIFT + ENTER as you would normally have to for arrays.

Please note that all the above random numbers will be regenerated each time the workbook is opened or changed, unless the calculation options are set to manual.

 

Google Sheets –Random Date Generator

All of the above examples work exactly the same in Google Sheets as in Excel.

random date generator Google Function