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.

**Table of Contents**Expand

## 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.

### 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 the 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 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.

- 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 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.

- 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")`

- 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.

- 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`

- 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**