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