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.

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 ()…