Download the example workbook
This tutorial will demonstrate how to generate random dates in Excel and Google Sheets.
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.
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.
You can use the DATE function to generate your limit dates, or you can refer to date cells instead:
Don’t forget to format your result cell to display the generated number as a date.
Generate Random Workdays
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.
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:
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.