< Go Back

Generate a Random Date Between Two Dates in Excel

Excel’s RANDBETWEEN function is used to generate numeric values between two specified numeric values. As dates are stored as serial numbers in Excel, so you can generate a random date between two dates using RANDBETWEEN function together with the DATE function in a formula.

Random date between two dates using RANDBETWEEN

The general formula to generate the random date between two dates is:


=RANDBETWEEN(date1, date2)

OR

=RANDBETWEEN(lower_date, upper_date)

Explanation of the formula

As the RANDBETWEEN function accepts two numeric values as the bottom and top value arguments, therefore the DATE function is used to generate a lower date value and upper date value as the bottom and top value arguments. So, the generic formula to return a random date between two dates will be:

=RANDBETWEEN(DATE(year,month,day), DATE(year,month,day))

To get the accurate results of a random date between two dates using the above formula, the following points must be remembered:

  1. Targeted cells range should be formatted as a date to display the random date between two dates correctly.
  2. Once random dates are generated in the targeted cells range, this formula will generate new random dates whenever you edit any cell having this formula or you open the worksheet again.
  3. To avoid generating the random date between two dates again, simply copy the formulas and replace with values using Paste Special > Values feature.

Example of RANDBETWEEN

For example, you want to generate a random date between two dates values like between Jan 1, 2019, and Dec 31, 2019. First, you need to select the cells range where you want to generate the random date between two dates and format the cells as a date. Then apply this formula in the first cell of range and copy the same formula to other cells of range.

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

 

Figure 1. Random_between-two-dates

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar