You can use array-based or non-array-based solutions to increment time in Google Sheets by minutes or hours from a start time.

I prefer array-based formulas, as they can quickly generate a series of incremental times in a column or row.

An incremented time series is very useful for recording events that occur at regular intervals, such as every 15 minutes, 30 minutes, or 1 hour. Let’s begin with the non-array-based formulas.

Non-Array Formulas to Increment Time in Google Sheets

If you aim to generate a series of incremental times in a column or row, you can utilize the following non-array formulas.

1-Hour Intervals (Non-Array Formula)

If you wish to create an incremental series of times in a column from 00:00:00 to 23:00:00 (covering a full 24-hour period), input the following formula into any cell and then drag the fill handle downwards until you reach your desired time:

=TIME(ROW(A1)-1, 0, 0)

This formula follows the syntax TIME(hour, minute, second), where ROW(A1) returns 1, making the initial hour component 0.

When you drag the formula down, the row number increments, resulting in 1, 2, 3, 4, and so forth.

The time returned by the formula will likely be in a 24-hour format. You can change this to a 12-hour format (AM/PM) by accessing the Format menu.

Select the result cells and go to Format > Number > Custom Number Format. Enter h:mm am/pm in the given field and click Apply.

If you want to start the time from 08:00 AM, adjust the formula as follows:

=TIME(ROW(A1)+7, 0, 0)

How to Generate the Result Across a Row:

If you want the 1-hour interval time series across the row, replace the ROW function in the formula with the COLUMN function.

15 or 30-Minute Intervals (Non-Array Formula)

Assume you want to increment the time by 30 minutes starting from 00:00:00 and ending at 23:00:00. How do you do that?

Enter the following formula in any cell and drag it down:

=TIME(0, (ROW(A1)-1)*30, 0)

In this formula, the ‘hour’ is 0, the ‘minute’ is calculated as (ROW(A1)-1)*30, which returns 0 initially and increases by 30 for each row when dragged down, and the ‘second’ remains 0.

To generate a time series starting at 8:00 AM, use the following formula:

=TIME(8, (ROW(A1)-1)*30, 0)

This adjustment involves replacing 0 (hour) with 8.

To get time increments by 15 minutes, use the following formula:

=TIME(0, (ROW(A1)-1)*15, 0)

This formula generates a sequence of time starting from 00:00:00. If you wish to start at a specific time, such as 08:00 AM, replace 0 (hour) with 8.

How to Generate the Result Across a Row:

To generate the 15/30 minute interval time series across a row, replace the ROW function with the COLUMN function in all the above formulas. Then drag across instead of dragging down.

Array Formulas to Increment Time in Google Sheets

As I mentioned at the beginning, incrementing time by minutes and hours is easy with array formulas in Google Sheets because we can use the SEQUENCE function.

Syntax:

SEQUENCE(rows, [columns], [start], [step])

In this function, you need to specify the number of rows or columns you want in the output, the start time, and the interval between each time.

1-Hour Intervals (Array Formula)

For example, if you want a time series starting from 00:00:00 and ending at 23:00:00 in a column, you should specify:

• rows: 24
• columns: 1
• start: TIME(0, 0, 0)
• step: TIME(1, 0, 0)

When you want the results in a row, it should be:

• rows: 1
• columns: 24
• start: TIME(0, 0, 0)
• step: TIME(1, 0, 0)

Here are the formulas based on this:

=SEQUENCE(24, 1, TIME(0, 0, 0), TIME(1, 0, 0))  // for results in a column
=SEQUENCE(1, 24, TIME(0, 0, 0), TIME(1, 0, 0))  // for results in a row

The formulas will generate time values. To format them, select the result range and choose Format > Number > Time.

15 or 30-Minute Intervals (Array Formula)

To increment time by 15 or 30 minutes using the SEQUENCE function, in the previous formulas, replace the ‘step’ value TIME(1, 0, 0) with TIME(0, 15, 0) or TIME(0, 30, 0), respectively.

Additionally, adjust the number of rows or columns to 48 (for 30-minute intervals) or 96 (for 15-minute intervals):

=SEQUENCE(48, 1, TIME(0, 0, 0), TIME(0, 30, 0))  // for results in a column
=SEQUENCE(1, 48, TIME(0, 0, 0), TIME(0, 30, 0))  // for results in a row
=SEQUENCE(86, 1, TIME(0, 0, 0), TIME(0, 15, 0))  // for results in a column
=SEQUENCE(1, 86, TIME(0, 0, 0), TIME(0, 15, 0))  // for results in a row

How to Change the Start Time of the Series:

In all the SEQUENCE formulas, you can specify a start time other than 00:00:00 by replacing TIME(0, 0, 0) (‘start’). For example, to start the time series from 8:00 AM, replace it with TIME(8, 0, 0).

Additionally, adjust the number of rows (‘rows’ argument) or columns (‘columns’ argument) accordingly.

Resources

Here are some related Google Sheets resources:

• How to Highlight Current Time in Google Sheets
• How to Increment DateTime by One Hour in Google Sheets
• Create Custom Time Slot Sequences in Google Sheets
• How to Highlight Cells Based on Expiry Date in Google Sheets
• Highlight Earliest Events Based on Date Column in Google Sheets
• How to Highlight Recurring Events or Payment Dates in Google Sheets
• Date-Related Conditional Formatting Rules in Google Sheets