How to Generate Random Date and Time in Excel (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

For many demonstration or experimental purposes, you may want to fill up Excel columns with random dates and times. You of course can fill those dates manually, but using an automatic formula can be quite handy. How you can generate random date and time in Excel is shown below with practical demonstrations.


How to Generate Random Date and Time in Excel: 3 Ways

In the below dataset, you can see a column that is filled with the date and time altogether. They are also chosen randomly using different combinations of formulas. How we get to this column full of random dates and times is going to be discussed here elaborately.

Insert date and time in Excel


1. Generate Random Date and Time Together in Excel

You can generate and show the date and time together in one cell following the below three methods.


1.1 Using TEXT Function

The TEXT function is one of the most popular text formatting functions. Integrating with the RAND function you can easily format and show dates and texts in Excel.

Steps

  • Select cell B5, and enter the following formula:
=TEXT(RAND()*("2021-2-10 12:00:00"-"2020-10-19:00")+"2020-10-19:00:00","YYYY-MM-DD HH:MM:SS")
  • After entering the formula, you will notice that cell B5 now has a random time and date.

Using TEXT Function to generate random time and date in Excel

  • After this, drag the Fill handle icon in the corner of cell B5 and drag it to cell B10.
  • Then you will see that the range of cells B5:B10 is now filled with random dates and times.

Using TEXT Function to generate random time and date in Excel

How Does the Formula Actually Work?

1. RAND()*(“2021-2-10 11:00:00”-“2020-10-1 8:00″)+”2020-10-1 8:00:00”: This formula returns a random value from 2021-2-10 11:00:00 to 2020-10-1 8:00:00.

2. TEXT(RAND()*(“2021-2-10 12:00:00”-“2020-10-1 9:00″)+”2020-10-1 9:00:00″,”YYYY-MM-DD HH:MM:SS”): This formula will take the value returned by function RAND and will return in date and time format as mentioned in the second argument of the TEXT functions.


1.2  Applying RANDBETWEEN Function

The RANDBETWEEN function is another version of the RAND function, where you can specify the range of randomness, meaning you can specify the lowest and highest limit of the random variable. Therefore, you will be able to generate the random date and time in Excel by combining these two functions.

Steps

  • Select cell B5 and enter the following formula:
=(RANDBETWEEN(--"2013-05-01",--"2013-05-31")+RAND()*("9:59"-"8:00")+"8:00")
  • After entering the formula, you will notice that there is only one date appearing in the cell. But we need both the date and time.

Applying RANDBETWEEN Function to Generate Random Date and Time Together in Excel

  • To resolve this, we first need to fill the cells with the dates by using the Fill Handle icon.

Applying RANDBETWEEN Function to Generate Random Date and Time Together in Excel

  • Then select the cells, and right-click the mouse from the context menu. Select Format Cells.

Applying RANDBETWEEN Function to Generate Random Date and Time Together in Excel

  • A new window will open, from that window go to the Number tab, then from the Category group, select Date.
  • From the Date option, in the Type field, select the date format that contains both date and time.
  • Click OK after this.

Applying RANDBETWEEN Function to Generate Random Date and Time Together in Excel

  • After clicking OK, you will notice all of the cells previously containing dates now have both date and time.

Applying RANDBETWEEN Function to Generate Random Date and Time Together in Excel

How Does the Formula Actually work?

1. RANDBETWEEN(–“2014-06-01”,–“2014-05-21”: This function will return date values between 2014-05-21 and 2014-06-01.

2. RAND()*(“9:49”-“7:00″)+”7:00”): This formula will return any random integer in between 9:59″-“8:00.


1.3 Combine DATE with RAND Function

You can easily display the date and time together by using the RAND function integrated with the DATE function.

Steps

  • Select cell B5 and enter the following formula:
=DATE(2000,1,1)+(RAND()*DATE(2013,6,28)-(DATE(2000,1,1)))
  • After entering the formula you will notice that cell B5 now has a random time and date.

Combine DATE with RAND Function to Generate Random Date and Time Together in Excel

  • After this, drag the Fill Handle icon in the corner of cell B5 and drag it to cell B10.
  • Then you will see that the range of cells B5:B10 is now filled with random dates and times.

How Does the Formula Actually Work?

1. DATE(2013,6,28)-(DATE(2000,1,1): In this formula, the DATE function takes arguments such as year, month, and day and returns those as the standard date format.

2. RAND()*DATE(2013,6,28)-(DATE(2000,1,1)): This formula will return random date values between the 1/1/2000 and 28/6/2013.


2. Generate Random Date Only in Excel

You can use the RANDBETWEEN function to generate a random date in between the DATE function range.

Steps

  • Select cell B5 and enter the following formula:
=RANDBETWEEN(DATE(2000,1,1),DATE(2013,6,28))
  • After entering the formula, you will notice that cell B5 now has a random date.

  • After this, drag the Fill Handle icon in the corner of cell B5 and drag it to cell B10.
  • Then you will see that the range of cells B5:B10 is now filled with random dates.

How Does the Formula Actually Work?

1. DATE(2000,1,1),DATE(2013,6,28): DATE function takes a year, month, day as argument and returns values in standard date format.

2. RANDBETWEEN(DATE(2000,1,1),DATE(2013,6,28)): This formula will return random dates between 1/1/2000 and 28/6/2013 mentioned in the Date function.


3. Create Random Time Only in Excel

Below you will learn to generate the random time only in 4 different ways.


3.1 Using TEXT and RAND Functions

The TEXT function is a handy function to generate random time and display. We combined it with the RAND function to get the random time only.

Steps

  • Select cell B5 and enter the following formula:
=TEXT(RAND()*(15-11)/24+11/24,"HH:MM:SS")
  • After entering the formula you will notice that cell B5 now has a random time.

Using TEXT and RAND Functions to Generate Random Time Only in Excel

  • After this, drag the Fill Handle icon in the corner of cell B5 and drag it to cell B10.
  • Then you will see that the range of cells B5:B10 is now filled with random times.

How Does the Formula Actually Work?

1. RAND()*(13-11)/24+11/24: This formula will return a random value in 24hr time format between  11 o’clock and 13 o’clock.

2. TEXT(RAND()*(15-11)/24+11/24, “HH:MM: SS”): This formula will return the values returned by the RAND function in standard HH: MM: SS format.


3.2 Combining TEXT and FLOOR Functions

Sometimes we need to generate time but we need to generate it with a time interval too. We can do that efficiently by using TEXT and FLOOR functions.

Steps

  • Select cell B5 and enter the following formula:
=TEXT(FLOOR(RAND(),"0:15"),"HH:MM:SS")

Combining TEXT and FLOOR Functions to Generate Random Time Only in Excel

  • After entering the formula you will notice that cell B5 now has a random time.
  • After this, drag the Fill Handle icon in the corner of cell B5 and drag it to cell B10.
  • Then you will see that the range of cells B5:B10 is now filled with random times.

  • If you notice, all of the time is actually 15 minutes apart from each other.

How Does the Formula Actually Work?

1. RAND(): This function will return any random value.

2. FLOOR(RAND(),”0:15”): This will round down the value returned from the RAND function close to the multiple of 0:15.

3. TEXT(FLOOR(RAND(),”0:15″),”HH:MM: SS”): Now, the output of the rounded value from the FLOOR function will be formatted in the standard HH:MM: SS format.


3.3 Utilizing RAND and TIME Functions

Just like the RANDBETWEEN function, you can use the Time-integrated version of the RAND function to generate time. We are combining it with the TIME function.

Steps

  • Select cell B5 and enter the following formula:
=TIME(8,0,0)+RAND()*(TIME(17,0,0)-TIME(8,0,0))
  • After entering the formula you will notice that cell B5 now has a random time.

Using TEXT and RAND Functions to Generate Random Time Only in Excel

  • After this, drag the Fill Handle icon in the corner of cell B5 and drag it to cell B10.
  • Then you will see that the range of cells B5:B10 is now filled with random times.

How Does the Formula Actually Work?

1. Time(17,0,0)-Time(8,0,0): This function will take an hour and a minute a second as arguments and return the values in standard time format.

2. RAND()*(TIME(17,0,0)-TIME(8,0,0): Here, the RAND function will return a random value in time format between 8 o’clock and 17 o’clock.


3.4 Applying RANDBETWEEN and TIME Functions

Using the RANDBETWEEN function, you can generate random time in between values returned by the TIME function.

Steps

  • Select cell B5 and enter the following formula:
=RANDBETWEEN(TIME(8,0,0)*10000,TIME(17,0,0)*10000)/10000

Applying RANDBETWEEN and TIME Function to Generate Random Time Only in Excel

  • After entering the formula you will notice that cell B5 now has a random time.
  • After this, drag the Fill Handle icon in the corner of cell B5 and drag it to cell B10.

  • Then you will see that the range of cells B5:B10 is now filled with random times.

How Does the Formula Actually Work?

1. TIME(8,0,0)*10000: This function will take an hour, minute, and second as its arguments and returns time in standard time format. For the sake of formatting, it is multiplied by 10000 and then divided by 10000 in a later stage.

2. RANDBETWEEN(TIME(8,0,0)*10000,TIME(17,0,0)*10000)/10000: In this function, RANDBETWEEN will return any random value in time format in between 8 o’ clock and 17 o’ clock.


Download Practice Workbook

Download this practice workbook below.


Conclusion

To sum it up, the question “how to generate the random date and time in Excel” is answered here in 8 different ways. But they are grouped here into two major groups. We used various combinations of TEXT, RANDBETWEEN, RAND, FLOOR, TIME, DATE functions. Among all of the methods used here, using the TEXT formula is easier to understand and implement.

For this problem, a practice workbook is available where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciated.


<< Go Back to Random Number in Excel | Randomize in Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo