Rubayed Razib Suprov
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.
Download Practice Workbook
Download this practice workbook below.
3 Ways to Generate Random Date and Time in Excel
In the below dataset, you can see a column that is filled with the date and time altogether. And 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.
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.
- After this, drag the Fill handle icon in the corner of cell B5 and drag it to cell B10.
- Then you will see that range of cells B5:B10 is now filled with random dates and times.
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 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.
- To resolve this, we first need to fill the cells with the dates by using the Fill Handle icon.
- Then select the cells, and right-click the mouse from the context menu. Select Format Cells.
- 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.
- After clicking OK, you will notice all of the cells previously containing dates now have both date and time.
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.
- After this, drag the Fill Handle icon in the corner of cell B5 and drag it to cell B10.
- Then you will see that 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.
Read More: How to Generate a Random String from a List in Excel (5 Suitable Ways)
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 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.
Read More: Excel VBA: Random Selection from List (3 Examples)
3. Create Random Time Only in Excel
Below here 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.
- After this, drag the Fill Handle icon in the corner of cell B5 and drag it to cell B10.
- Then you will see that 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")
- 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 are 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.
- After this, drag the Fill Handle icon in the corner of cell B5 and drag it to cell B10.
- Then you will see that 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 returns 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
- 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 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.
Read More: Random Selection Based on Criteria in Excel (3 Cases)
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 appreciable.
Related Articles
- Random Selection from List with No Duplicates in Excel (5 Cases)
- How to Select a Random Sample from a Population in Excel (3 Ways)
- Make a Random Selection from List Without Repetition in Excel
- How to Randomly Select Rows in Excel (2 Ways)
- How to Select Random Sample in Excel (4 Methods)
- How to Freeze Random Selection in Excel
