In some special situations we may need to generate some random times in worksheet. We can enter time manually and one by one, but if we want to require a lot of random dates in excel worksheet, we will spend a lot of time on manual entry, and this work is very boring.

We need to find out a quick and convenient way to insert random times in excel, this tutorial will help you to solve this problem by enter formula with **TEXT** and **RAND **functions and **VBA Code**.

Table of Contents

## 1. Generate Random Time in Excel

**Step1:** Select one cell, enter the formula:

` =TEXT(RAND(),"HH:MM:SS")`

The **RAND** function returns a random number, **TEXT** function can convert number to a proper format, in this formula we convert number to HH:MM:SS format.

**Step2:** Click **Enter** to get the result. Verify that a random time is displayed.

**Step3:** We just need to drag fill handle down to create more random times. You can also copy this formula to other cells to get more random times.

**Comment:**

Actually, you can also only use **RAND** function itself to get a random time here. For example, enter **=RAND()** in cell A1. Then we get a real number.

In **Home** ribbon, click **General** dropdown list in **Number** group.

Select **Time** format.

Then we will get a random time properly.

## 2. Generate Random Time Between Two Times in Excel

**Step1:** For RAND function, if you want to generate a number between a and b, we can use **=RAND()*(b-a)+a** to get a random number. So if we want to get a random time between a and b, we can use formula **=TEXT(RAND()*(b-a)/24+a/24,”HH:MM:SS”)**. So, if we want get a random time between 7 to 13 o’clock, in cell A1, enter：

`=TEXT(RAND()*(13-7)/24+7/24,"HH:MM:SS")`

In above screenshot, we can see that returned random time 10:33:13 can meet our expectation. As we mentioned above, we can also only use **RAND **function here as well, enter **=RAND()*(13-7)/24+7/24**, then convert it to time format.

**Step2:** Drag fill handle down. Verify that random times between time 7 and 13 o’clock are displayed properly.

## 3. Generate Random Date and Time in Excel

**Step1:** Refer to above parts, we can update the formula to get date and time properly. Enter the formula

`=TEXT(RAND()*("1/1/2019 7:00:00" - "12/31/2019 13:00:00")+"12/31/2019 13:00:00","MM/DD/YYYY HH:MM:SS")`

Verify that a random date and time is returned properly. You can change start and end date time to meet your demands.

## 4. Generate Random Time with VBA Code

To generate random time values in Excel using VBA code, you can use the following steps:

**Step1:** Press the **‘Alt + F11’** keys to open the VBA Editor.

**Step2:** Click on **‘Insert’** in the VBA menu and select **‘Module’**.

**Step3:** In the new module window, enter the following VBA code:

Function RandomTime_excelhow() As Date Randomize RandomTime_excelhow = TimeSerial(Int(Rnd() * 24), Int(Rnd() * 60), Int(Rnd() * 60)) End Function

**Note:**this code uses the

**TimeSerial**

**function**to create a random time value. The

**Int**and

**Rnd**functions are used to generate random integer values for the hour, minute, and second components of the time.

**Step4:** Save the module and return to the Excel workbook. In any blank cell, enter the following formula to generate a random time value.

`=RandomTime_excelhow()`

You can also use the VBA code to generate random time values in a range of cells by using a loop to iterate over the cells and calling the RandomTime function for each cell.

Sub GenerateRandomTime() Dim rng As Range Set rng = Application.InputBox("Select the range you want to generate random time values", Type:=8) Dim startTime As Date startTime = TimeSerial(9, 0, 0) 'Start time is 9:00 AM Dim endTime As Date endTime = TimeSerial(17, 0, 0) 'End time is 5:00 PM Dim cell As Range For Each cell In rng cell.Value = startTime + TimeSerial(Int(Rnd() * (endTime - startTime)), Int(Rnd() * 60), Int(Rnd() * 60)) Next cell End Sub

## 5. Video: Generate Random Time in Excel

In this video, I will show you how to generate random time values in Excel using both formulas and VBA code.

## 6. Related Functions

- Excel Text function

The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)… - Excel RAND function

The Excel RAND function returns a random real number that is greater than or equal to 0 and less than 1. The syntax of the RAND function is as below:=RAND ()…