How to generate random date between two dates quickly in Excel?
When you use an Excel file, sometimes you want to generate random date for some purpose, of course, you can enter the date manually one by one, but if you need to insert multiple dates, this method will be time-consuming and boring. How to quickly generate random date on earth in Excel?
Generate random date between two dates in cells with formula
Generate random date between two dates in cells a with useful feature
Comparison of Methods for Generating Random Dates Between Two Dates in Excel
To generate random dates between two specified dates in Excel, you can use a formula combining RANDBETWEEN and DATE or use Kutools for Excel's Insert Random Data feature. Both methods let you define a date range, but they differ in date-type options, duplicate control, and setup.
| Comparison | Excel Formula | Kutools Insert Random Data |
|---|---|---|
| Date range | Specify the start and end dates inside the RANDBETWEEN and DATE formula | Specify the start and end dates directly in the Date tab |
| Date type | Generates general random dates; a separate WORKDAY-based formula can be used to exclude weekends | Choose regular dates, workdays, or weekend dates directly |
| Unique dates | ✘ The formulas shown do not prevent duplicate dates | ✔ Unique dates can be selected when non-repeating dates are required |
| Setup | Enter the formula, fill it down, and then format the resulting serial numbers as dates | Select the cells, open Insert Random Data, choose Date, set the date range and options, and click OK |
Generate random date between two dates in cells with formula
In Excel, you can mix the Randbetween and Date function to create random date, please do as follows:
1. Select a cell that you want to insert a random date, and enter this formula:
Note: in the function, (2019, 1, 1) is the starting date, and (2019, 10, 20) is the ending date, you can replace them as you need.
2. Then, drag the fill handle down to the cells that you want to fill this formula, it displays a five-digit number in the cell, see screenshot:
3. And then, you should format the numbers to date format, please select the cell, and right-click, choose Format Cells from the context menu.
4. In the Format Cells dialog box, click Number tab, and select Date from the Category, then select a date format you need from the Type drop-down list. See screenshot:
5. Click OK, the numbers have been converted to the normal dates. And the dates between the given two dates have been generated randomly. See screenshot:
- Tips: If you want to generate random workdays excluding the weekends, please apply the below formula:
- =WORKDAY(RANDBETWEEN(DATE(2019, 1, 1),DATE(2019, 10, 1))-1,1)
Generate random date between two dates in cells a with useful feature
It seems that the above method is some troublesome, is there an easier and quicker way to solve this problem? Don’t worry, with Kutools for Excel’s Insert Random Data tool, you can quickly insert the random date. Click to download Kutools for Excel!
After installing Kutools for Excel, please do as this:
1. Select a list of cells that you want to insert the random date.
2. Click Kutools > Insert > Insert Random Data, see screenshot:
3. In the popping up Insert Random Data dialog box, click Date tab, then specify the date scope, and choose the date type (workday date, weekend date or unique date) as you need. Then, click OK button, and the dates between the specific two dates have been inserted randomly. See screenshots:
 |  |  |
Click to Download Kutools for Excel and free trial Now!
Conclusion
Excel formulas provide a built-in way to generate random dates between two specified dates. You can combine RANDBETWEEN with DATE to generate the underlying date serial numbers and then format the results as dates. If weekends need to be excluded, a separate WORKDAY-based formula can be used. This method is suitable when you prefer a formula-based solution and only need basic random-date generation.
Kutools for Excel's Insert Random Data provides a more direct and flexible approach. After selecting the target cells, you can specify the date range in the Date tab and choose whether to generate regular random dates, workdays, weekend dates, or unique dates. This eliminates the need to build different formulas or manually convert the generated serial numbers to date format.
More relative random data articles:
- Insert Random (Integer) Numbers Between Two Numbers
- Do you need to insert random numbers in Microsoft Excel? There are several methods to insert random numbers in a range as follows.
- Generate Random Character Strings In A Range
- Sometimes you may need to generate random strings in cells, such as different passwords. This article tries to show you some tricks to generate different random strings in Excel.
- Generate Random Time In Excel
- In Excel, in a common situation, most of us may need to insert random numbers, date or text strings as our desired. But sometimes, have you ever tried to insert times randomly in a range of cells? Actually, we can apply some formulas to insert random time into the worksheet.
- Generate Random Yes Or No In Excel
- In some special cases, we may want to generate random Yes or No in a range in Excel, here I will tell you some formulas to quickly generate random Yes or No in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in