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