In the dataset below, you can see a column filled with dates and times. They have been chosen randomly using different combinations of formulas. Let’s demonstrate how you can achieve that.
Method 1 – Generate a Random Date and Time Together in Excel
Case 1.1 – Using TEXT and RAND Functions
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")- The cell B5 gets a random time and date.
- Click and hold the Fill handle icon in the corner of cell B5 and drag it to cell B10.
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.
Case 1.2 – Applying the RANDBETWEEN Function
Steps
- Select cell B5 and enter the following formula:
=(RANDBETWEEN(--"2013-05-01",--"2013-05-31")+RAND()*("9:59"-"8:00")+"8:00")- The formula creates only dates.
- To resolve this, fill the cells with the dates by using the Fill Handle icon.
- Right-click and select Format Cells.
- In the Format Cells window, go to the Number tab, and from the Category group, select Date.
- From the Date option, in the Type field, select the date format that contains both the date and time.
- Click OK.
- All the cells will get both a date and a 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.
Case 1.3 – Combine DATE with the RAND Function
Steps
- Select cell B5 and enter the following formula:
=DATE(2000,1,1)+(RAND()*DATE(2013,6,28)-(DATE(2000,1,1)))- Cell B5 gets a random time and date.
- Click and hold the Fill Handle icon in the corner of cell B5 and drag it to cell B10.
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.
Method 2 – Generate a Random Date Only in Excel
Steps
- Select cell B5 and enter the following formula:
=RANDBETWEEN(DATE(2000,1,1),DATE(2013,6,28))- Cell B5 gets a random date.
- Drag the Fill Handle icon in the corner of cell B5 to cell B10.
- The range of cells B5:B10 gets 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.
Method 3 – Create a Random Time Only in Excel
Case 3.1 – Using TEXT and RAND Functions
Steps
- Select cell B5 and enter the following formula:
=TEXT(RAND()*(15-11)/24+11/24,"HH:MM:SS")- Cell B5 gets a random time.
- Drag the Fill Handle icon from cell B5 to cell B10.
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.
Case 3.2 – Combining TEXT and FLOOR Functions
Steps
- Select cell B5 and enter the following formula:
=TEXT(FLOOR(RAND(),"0:15"),"HH:MM:SS")
- Drag the Fill Handle icon from the corner of cell B5 to cell B10.
- All of the time are at least 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.
Case 3.3 – Utilizing RAND and TIME Functions
Let’s generate times between 8 a.m. and 5 p.m.
Steps
- Select cell B5 and enter the following formula:
=TIME(8,0,0)+RAND()*(TIME(17,0,0)-TIME(8,0,0))- Cell B5 gets a random time.
- Drag the Fill Handle icon to cell B10.
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.
Case 3.4 – Applying RANDBETWEEN and TIME Functions
Steps
- Select cell B5 and enter the following formula:
=RANDBETWEEN(TIME(8,0,0)*10000,TIME(17,0,0)*10000)/10000
- Drag the Fill Handle icon to cell B10.
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 the Practice Workbook
<< Go Back to Random Number in Excel | Randomize in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
