How to Generate Random Date in Excel

In some special situations we may need to generate some random dates in worksheet. We can enter date manually, but if we want to require a lot of random dates, we will spend a lot of time on manual entry, and this work is very boring. Above all, we need to find out a quick and convenient way to insert random dates in excel, this tutorial will help you to solve this problem by enter formula with RANDBEWEEN and DATE functions.

Generate Random Date in Excel


Step 1: Select one cell, enter the formula =RANDBETWEEN(DATE(2019,1,1),DATE(2019,12,31)). RANDBETWEEN function returns data between bottom and top, DATE function returns a date. This formula will return a random date between 2019,1,1 and 2019,12,31.

Generate Random Date 1

Step 2: Click Enter to get the result. Verify that five digits number 43617 is displayed.

Generate Random Date 2

Step 3: In Home ribbon, click Generate dropdown list in Number group.

Generate Random Date 3

Step 4: Select Short Date or Long Date format depends on your requirement.

Generate Random Date 4

Step 5: Verify that previous number is converted to date format properly. And this date is a random date between start and end dates we entered in formula.

Generate Random Date 5

Step 6: For the purpose generate multiple random dates, we just need to drag fill handle down to create more random dates. You can also copy this formula to other cells to get more random dates.

Generate Random Date 6

Comment:

1. After step 2, you can also change number to other custom date format in Format Settings as well.

Right click to load menu, select Format Cells.

Generate Random Date 7

Under Number tab, select Date in Category, select date format in Type, for example YYYY-MM-DD.

Generate Random Date 8

After above setting, click OK. Verify that number is changed to date format properly.

Generate Random Date 9

2.This formula returns a date between two entered dates; it can be a weekday or weekend. If you want to only generate weekday exclude weekends, you can enter this formula =WORKDAY(RANDBETWEEN(DATE(2019,1,1),DATE(2019,12,31))-1,1). Then you can find that generated random dates are weekdays.

Related Functions


  • Excel RANDBETWEEN Function
    The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
Related Posts

How to Count Cells between Two Dates or Times in Excel
count cells between dates formula3

This post will guide you how to count cells between two dates using a formula in Excel 2013/2016. How do I count times between a given time or date range based on a condition in Excel. Is there a easy ...

How to Sum Values Based on Month and Year in Excel
Sum Values Based on Month 6

We often do some summary or statistic at the end of one month or one year. In these summary tables, there are at least two columns, one column records the date, and the other column records the sales or product ...

How to Convert Text to Time in Excel
convert text to time7

This post will guide you how to convert time string to time in Excel. Assuming that you have a list of text string in your worksheet, and you wish to convert these time string to standard time format, how to ...

How to Calculate Remaining Days in a Month or Year in Excel
calculate remaining days5

This post will guide you how to calculate remaining days in a given month or year in Excel. How do I calculate the number of days left in a month or year using a formula in Excel 2013/2016. Calculate Remaining ...

How to Convert mmddyy to Date in Excel
convert mmddyyy to date9

This post will guide you how to convert non-standard date formats or text to a standard date in Excel. If you have a date with mmddyy format, and you want to change it as the normal date format as mm/dd/yyyy ...

How to VLOOKUP to Return Value if Date Falls between Two dates in Excel
vlook value if date falls two dates1

This post will guide you how to do a VLOOKUP between two dates and return corresponding value in Excel. How do I VLOOKUP between two dates and return corresponding value with lookup formula in Excel. How to lookup a value ...

Convert Excel Date to Unix Time Stamp (or Time Stamp to Excel Date)
convert date to unix timestamp5

This post will guide you how to convert a date string to a Unix time stamp in Excel. How do I convert excel date to Unix timestamp with a formula in Excel. How to convert Unix time stamp to an ...

Converting Week Number to Date
convert week to date4

This post will guide you how to convert a week number to a date with a formula in Excel. How do I calculate a date from a week number and a year with formula in Excel. Or how to get ...

Convert Month Name to Number
convert month name to number6

This post will guide you how to convert month name to number in excel. How do I convert month name to number with an excel formula. How to convert month name to number with an Excel VBA macro. How to ...

Get the First Monday of a Given Year
get first monday of the year2

This post will guide you how to get the first Monday of a given year or date in excel. How do I calculate the first Monday of a specific year in excel. How to determine the first Monday in a ...

Sidebar