Table of Contents
To generate random dates in Excel, you can use the built-in function “=RAND()” and format it as a date. This will generate a random decimal number between 0 and 1. You can then multiply this number by the range of dates you want to generate and add it to the starting date. For example, if you want to generate dates between January 1st, 2021 and December 31st, 2021, you would use the formula “=RAND()*(DATE(2021,12,31)-DATE(2021,1,1))+DATE(2021,1,1)”. This will give you a random date within the specified range. You can also use the “RANDBETWEEN()” function to generate a random integer and format it as a date. Simply specify the starting and ending date range within the brackets, and it will give you a random date within that range. These methods can be useful for generating random dates for various purposes, such as testing, data analysis, and more.
Generate Random Dates in Excel (With Examples)
You can use the following formula to generate a random date between a specific date range in Excel:
=RANDBETWEEN($A$2,$B$2)
This formula assumes that the start date is in cell A2 and the end date is in cell B2.
By default, Excel will display this random date as a numeric value but you can use the Number Format feature in Excel to instead display it as a date value.
The following example shows how to generate random dates in Excel in practice.
Example: Generate Random Dates in Excel
Suppose we would like to generate a random date in Excel between 1/1/2000 and 12/31/2023.
We can type these start and end dates into cells A2 and B2, respectively, and then type the following formula into cell C2 to generate a random date between these two dates:
=RANDBETWEEN($A$2,$B$2)
The following screenshot shows how to use this formula in practice:
The RANDBETWEEN function generates a random date between this date range but the date is displayed as a numeric value by default.
Note: The numeric value represents the number of days since 1/1/1900.
To convert this numeric value to a date, we can highlight cell C2 and then click the Number Format dropdown menu on the Home tab, then click Short Date:
The numeric value will automatically be converted to a date:
Note that we can also click and drag this formula down to more cells in column C to generate several random dates between this date range:
Notice that all of the random dates are within the date range that we specified.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I generate random dates in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-generate-random-dates-in-excel/
stats writer. "How can I generate random dates in Excel?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-generate-random-dates-in-excel/.
stats writer. "How can I generate random dates in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-generate-random-dates-in-excel/.
stats writer (2024) 'How can I generate random dates in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-generate-random-dates-in-excel/.
[1] stats writer, "How can I generate random dates in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I generate random dates in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.