Creating time sequences, whether by hour, minute, or second increments in Excel, can be incredibly useful for a variety of purposes. For instance, you can use time sequences to create shift schedules in workplaces with rotating shifts, set up time slots for appointments, or track time-based events.

In Excel, you can use either a drag-down formula or a dynamic array formula to generate these sequences. Both options are straightforward to implement, but each has its own advantages depending on the complexity and the number of rows you need. This tutorial will walk you through both methods.

Additionally, the formulas provided work well with both regular times and timestamps (DateTime), making them versatile for many different scenarios.

How to Create Time Sequences in Excel by Hour Increments

In cell A2, enter your starting time or timestamp. For example, you can enter 13:00:00 in 24-hour format. Ensure that the cell is formatted as Time (you can right-click, choose Format Cells, and select Time).

In cell A3, enter the following formula:

=A2+IF(TIME(1, 0, 0)=0, 1, TIME(1, 0, 0))

This will increment the time by 1 hour. The TIME(1, 0, 0) portion of the formula tells Excel to add 1 hour (1 hour, 0 minutes, and 0 seconds).

If you need to increment by a different number of hours, simply replace the 1 with the desired number between 1 and 24. For instance, TIME(2, 0, 0) will increment by 2 hours.

Note that if you set the increment to 24 hours, the time will increment by one full day.

After entering the formula in A3, use the Fill Handle (the small square at the bottom-right corner of cell A3) to drag the formula down as far as you need to create the sequence.

If the value in A2 is a timestamp (including the date), use the Format Painter to apply the same formatting from A2 to the cells down, ensuring consistency in how the date and time are displayed.

Dynamic Array Formula for Hour Increment:

For a more advanced solution, you can use a dynamic array formula, which will automatically spill the results into multiple cells.

In cell A3, enter the following formula:

=LET(increment,TIME(1, 0, 0), SEQUENCE(100, 1, A2+IF(increment=0, 1, increment), IF(increment=0, 1, increment)))

This formula generates a sequence starting from the time in A2, incrementing by 1 hour at each step. The LET function defines the time increment, and the SEQUENCE function generates a vertical array of times.

Once the results are displayed, select the spilled range, right-click, and open the Format Cells dialog box. Choose Time or, if you want to display a timestamp, select Custom and enter dd-mm-yyyy hh:mm:ss. This formatting will also apply to the other two dynamic array formulas provided below.

The 100 in the formula specifies how many rows of data you want to generate. You can change this number to fit your needs.

The SEQUENCE function will automatically spill the results into 100 rows (or whatever number you specify).

You can replace TIME(1, 0, 0) with any number of hours between 1 and 24. If you want to increment by 12 hours, use TIME(12, 0, 0). Setting this to TIME(24, 0, 0) will increment by one full day.

How to Create Time Sequences in Excel by Minute Increments

Assume the time or timestamp or time is in cell A2.

In cell A3, enter the following formula and drag it down to increment the time by 30 minutes:

=A2 + TIME(0, 30, 0)

This will add 30 minutes to the time in A2. You can replace 30 with any number from 1 to 60 to increment by different minute values. For example, TIME(0, 15, 0) will increment by 15 minutes, while TIME(0, 60, 0) will increment by 1 hour.

Dynamic Array Formula for Minute Increments:

To generate the time sequence using a dynamic array formula, enter the following in cell A3:

=LET(increment, TIME(0, 30, 0), SEQUENCE(100, 1, A2 + increment, increment))

This formula will generate a sequence of times starting from A2, incrementing by 30 minutes (or whatever number of minutes you define). The sequence will automatically spill into the cells below.

Like the hour increment formula, you can adjust the number of rows by replacing 100 with the desired value.

How to Create Time Sequences in Excel by Second Increments

Assume the time or timestamp is in cell A2.

In cell A3, enter the following formula to increment the time by 15 seconds:

=A2 + TIME(0, 0, 15)

This will increment the time by 15 seconds. You can replace 15 with any number from 1 to 60 to increment by other second intervals. For example, TIME(0, 0, 60) will increment by 1 minute.

After entering the formula in A3, drag the fill handle down to generate the time sequence for as many rows as you need.

Dynamic Array Formula for Second Increments:

To generate the time sequence with seconds increments, enter the following in cell A3:

=LET(increment, TIME(0, 0, 15), SEQUENCE(100, 1, A2 + increment, increment))

This formula will generate a sequence of times starting from A2, incrementing by 15 seconds (or any number of seconds you specify).

Just like the minute and hour increment formulas, the results will spill into the cells below, and you can adjust the number of rows by replacing 100 with your desired value.

Conclusion

Using Excel to generate time sequences with specific increments (by hour, minute, or second) is a straightforward process that can be tailored to suit various scenarios.

The drag-down formulas are specifically for use with Excel versions that don’t support dynamic array formulas and functions like LET and SEQUENCE.

Whether you use a drag-down formula or a dynamic array formula, both methods are effective for creating time-based sequences for shift schedules, appointments, data logging, and more.

By understanding these methods, you can customize the time increments based on your specific needs and even combine them with other Excel functions for more complex workflows.

Resources

• Excel Template for Hourly Time Slot Booking
• Hourly Time Slot Booking Template in Google Sheets
• How to Increment Time by Minutes or Hours in Google Sheets
• How to Increment DateTime by One Hour in Google Sheets (Array Formula)
• How to Add Hours, Minutes, Seconds to Time in Google Sheets
• Create Custom Time Slot Sequences in Google Sheets