How to quickly generate random time in Excel?
When working with Excel, it's common to insert random numbers, dates, or text strings for testing, data analysis, scheduling, or simulation purposes. However, there are scenarios where you might need to insert random times within a given range or at specific intervals — for example, creating mock timetables, filling attendance logs, or simulating time-based data for system tests. While Excel does not have a direct feature for random time generation, there are several practical solutions available, including built-in formulas, easy-to-use add-in features, and flexible VBA macro methods.
Generate random time in Excel with formulas
Generate random time in Excel with a handy feature
Generate random time in Excel with VBA code
Comparison of Methods for Generating Random Times in Excel
To generate random times in Excel, you can use built-in formulas, Kutools for Excel's Insert Random Data feature, or VBA. These methods differ in time-range control, special options, result behavior, and setup.
| Comparison | Excel Formulas | Kutools Insert Random Data | VBA |
|---|---|---|---|
| Time range | Generate random times for the whole day or customize the formula for a specific time range | Enter the exact start and end times in the From and To boxes | Enter the desired start and end hours when running the macro |
| Special options | Can generate times at specified intervals and random date-time values with customized formulas | Enable Unique values when non-repeating random times are required | Can exclude a specified hour and can be further customized through code |
| Result behavior | Dynamic — RAND-based results change when the worksheet recalculates; Paste Values is needed to keep them fixed | Static — generated times remain unchanged after insertion | Static values are written directly into the selected cells |
| Best for | Dynamic random times, interval-based times, and formula-based simulations | Quickly generating static or unique random times within a specified range | Customized or repeatable random-time generation with advanced rules |
Generate random time in Excel with formulas
The following formulas allow you to create random times according to your requirements, such as randomizing within the whole day, generating times between two specific times, or producing random times at defined intervals. These methods are ideal in most everyday Excel tasks, especially when you want instant results without enabling macros or installing add-ins.
Generate random time in Excel
To quickly fill a range with random times, enter the following formula in a blank cell. Then, drag the fill handle down to apply the formula to additional rows as needed. This provides a formatted random time value between 00:00:00 and 23:59:59.
You can copy this formula down as many rows as you need. Remember, RAND() generates a new value every time the worksheet recalculates, so the random times will update if any changes are made to the sheet. To preserve the generated times, copy the results and use Paste Values to overwrite the formulas.
Generate random time between two times in Excel
If you want to create random times within a restricted window, such as between 11:00 and 15:00, you can use the formula below. This method is well-suited for mock schedules, appointment systems, or simulations where time windows matter.
Enter the following formula in a blank cell. Drag the fill handle over the range where you need to fill the random times.
After the times are generated, if you want to keep them static, paste them as values. Be cautious: if your time values start/end exactly at midnight (00:00), make sure the formula uses 0 and 24 appropriately to avoid date wrap errors.
Generate random time at specific intervals in Excel
To randomly generate times at set intervals (such as every 15 or 30 minutes), you can combine FLOOR and RAND within the TEXT function. This is especially helpful for tasks like creating randomized bookings, setting up randomized event starts, or spacing mock data at precise increments.
Enter or copy this formula into a blank cell. Drag the fill handle down as needed to generate the quantity of random interval-based times you desire.
For better stability, convert results to values after generating them.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Generate random date and time between two datetimes in Excel
To generate both random date and time values—useful when populating random event logs or scheduling records—use the following formula. This approach combines date and time for more comprehensive test or simulation data.
Input or paste this formula in a cell, then drag down to fill more rows as required.
When dragging the formula down, each cell will generate a new random date and time within your chosen interval. Should you only need a date or only a time portion, adjust the formula format accordingly (for instance, use "YYYY-MM-DD" for date only).
Generate random time in Excel with a handy feature
If managing formulas seems tedious or you need to customize large-scale random data quickly, Kutools for Excel offers an Insert Random Data feature that enables you to insert random numbers, dates, times, and even custom text strings in just a few clicks. This is especially suitable if you frequently need random data, want to avoid formula recalculation, or need additional options such as inserting unique times within a range.
After installing Kutools for Excel, follow these instructions:
1. Select a blank range of cells for the random times.
2. Navigate to Kutools > Insert > Insert Random Data.
3. In the Insert Random Data dialog, go to the Time tab, type your desired start and end times in the From and To boxes respectively.
Tips: To ensure all generated times are different, check the Unique values option.
4. Click OK or Apply. Your selection will instantly be filled with random times.
Download and free trial Kutools for Excel Now !
The Insert Random Data feature is especially practical if your random times need to be static (won't change after insertion).
Generate random time in Excel with VBA code
Another flexible approach to generate random times in Excel is using VBA macros. By customizing VBA, you can include additional requirements—such as excluding specific hours, generating times at specified intervals, or updating random times automatically as the worksheet changes. Using a macro is particularly useful for users who need more control or want to automate random time generation for multiple ranges or repeating processes.
Below is a sample VBA macro that generates and fills random times in the cells you select. You can adjust the start/end times, the granularity of the randomization (minutes, seconds), or exclude certain hours as needed.
1. Open the VBA editor by clicking Developer > Visual Basic. In the VBA window, go to Insert > Module. Copy and paste the code below into the newly created module. (If the Developer tab is not displayed, enable it in Excel Options > Customize Ribbon.)
Sub GenerateRandomTimes()
Dim WorkRng As Range
Dim StartHour As Integer
Dim EndHour As Integer
Dim ExcludeHour As Integer
Dim cell As Range
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select range to fill random times:", xTitleId, WorkRng.Address, Type:=8)
StartHour = Application.InputBox("Start hour (0-23):", xTitleId, 8, Type:=1)
EndHour = Application.InputBox("End hour (1-24):", xTitleId, 18, Type:=1)
ExcludeHour = Application.InputBox("Hour to exclude (if none, type -1):", xTitleId, -1, Type:=1)
Application.ScreenUpdating = False
Randomize
For Each cell In WorkRng
Dim h As Integer, m As Integer, s As Integer
Do
h = Int((EndHour - StartHour) * Rnd + StartHour)
Loop While h = ExcludeHour
m = Int(60 * Rnd)
s = Int(60 * Rnd)
cell.Value = TimeSerial(h, m, s)
cell.NumberFormat = "hh:mm:ss"
Next cell
Application.ScreenUpdating = True
End Sub2. Run the macro by pressing F5 key. A prompt will appear to let you choose the target cell range. You can then set your desired start hour, end hour, and, if needed, any hour to exclude. Enter -1 if you do not want to exclude any specific hour. The macro will then insert unique random times in your selected range.
This VBA approach supports advanced customization such as omitting night hours or generating different intervals by changing how minutes or seconds are randomized. If you repeatedly run the macro on the same range, values will be overwritten—always copy results elsewhere if you wish to preserve them before a new execution.
Conclusion
Excel formulas offer flexible built-in options for random-time generation. RAND can be used to generate times across an entire day or within a specified time range, while FLOOR can be combined with RAND to generate times at fixed intervals such as every 15 or 30 minutes. However, RAND-based results recalculate automatically, so you need to convert them to values if the generated times should remain fixed.
Kutools for Excel's Insert Random Data provides a more direct approach when you need random times within a specified range. Simply select the cells, enter the start and end times in the Time tab, and enable Unique values if non-repeating times are required. The generated times are inserted as static values, eliminating the need to build formulas or convert formula results to values afterward.
VBA provides greater customization for specialized random-time generation. The macro shown in the article lets you specify the target range, start hour, end hour, and an hour to exclude. It can also be modified for additional requirements, making VBA suitable for automated or highly customized random-time generation when coding is acceptable.
More relative articles:
- Generate Random Date Between Two Dates
- When you use an Excel file, sometimes you want to generate random date for some purpose, of course, you can enter the date manually one by one, but if you need to insert multiple dates, this method will be time-consuming and boring. How to quickly generate random date on earth in Excel?
- Check If Time Is Between Two Times
- In Excel, how could you check if a specific time is between two given times? To check them one by one will waste lots of time, here, I will introduce some simple formulas to solve this job.
- Convert Time Format From12 Hour To24 Hour And Vice Versa
- When you work on Excel in your daily work, you may use different time format under different conditions, such as12 hour format and24 hour format as following screenshot shown. But, how could you convert time format from12 hour to24 hour and vice versa in Excel?
- Calculate Hours Between Times After Midnight
- Supposing you have a time table to record your work time, the time in Column A is the start time of today and time in Column B is the end time of the following day. Normally, if you calculate the time difference between the two times by directly minus "=B2-A2", it will not display the correct result as left screenshot shown. How could you calculate the hours between two times after midnight in Excel correctly?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in