Skip to main content

How to generate random date between two dates quickly in Excel?

Author: Xiaoyang Last Modified: 2020-04-24

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

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:

=RANDBETWEEN(DATE(2019, 1, 1),DATE(2019, 10, 20))

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 as below demo shown. Click to download Kutools for Excel!

Tips:To apply this Insert Random Date feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

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!

More relative random data articles:

  • 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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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!
Comments (6)
Rated 3.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
formülde hata var. aralara virgül değil noktalı virgül gelmeli.

=RANDBETWEEN(DATE(2019; 1; 1); DATE(2019; 10; 20))
Rated 3.5 out of 5
This comment was minimized by the moderator on the site
Hello, babalira,
Yes, as you said, in your language you should use semicolons in the formula, but in English language you should use commas.
Thank you!
This comment was minimized by the moderator on the site
lapolisia te baneara esta pagina panchito huevonazo
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
Random dates June 22 2018 July 3 2018 October 21 2022 June 1 2026 July 3 2045
This comment was minimized by the moderator on the site
NFL 2017 season 2 week Seattle Seahawks New England Patriots San Diego Chargers Washington redskins Los Angeles Rams Tampa bay Buccaneers Philadelphia Eagles Pittsburgh Steelers
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations