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:
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:
To get the accurate results of a random date between two dates using the above formula, the following points must be remembered:
- Targeted cells range should be formatted as a date to display the random date between two dates correctly.
- 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.
- 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.
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.