To increment time in Google Sheets, you can use array or non-array based solutions.

I prefer the array-based formula to increment time as it can generate a series of incremental time in a column in a flash.

The incremented time series can come in handy on certain occasions.

For example, you have some activities taking place at every 30 minutes or 1 hour, you can generate an incremented time series and record that.

Let’s start with the non-array based formulas.

Non-Array Formula to Increment Time in Google Sheets

Assume you want to generate a series of incremental time in column A, then you can use the below formula.

Please note the formula is different to increment hours and to increment minutes.

In the formulas, I am using the functions TIME, ROW and ARRAYFORMULA.

Hours Increments in Sheets (Non-Array Formula)

I want to get an incremental series of time in column a from 09:00:00 am to 05:00:00 pm. Here are the steps to generate that.

Steps:

Enter the below formula in cell A1 and drag the fill handle down.

=TIME(row(A9),0,0)

In this, the cell address A9 represents the starting time that is 9:00:00 am.

The time returned by the formula will probably be in 24 hr format. You can change that to 12 hr format (AM/PM) by accessing the Format menu.

Select the cell containing the incremental time series and go to Format > Number > More formats > More date and time format and apply the required time format.

Suppose you want to start the series from 01:00:00 pm to 11:00:00 pm. Then the formula must be used as follows.

=TIME(row(A13),0,0)

Minutes Increments in Sheets (Non-Array Formula)

In the above example, I have detailed how-to increment hours in Google Sheets. To increment minutes in Docs Sheets, you can use the below formula.

Assume you want to increment the time by 30 minutes starting from 6:00:00 am. How to do that?

Enter the time 6:00:00 am in cell A1. Enter the below formula in cell A2 and drag down.

=TIME(6,row(A1)*30,0)

Just change the ROW formula to row(A1)*20 to increment by 20 minutes.

This way you can increment the time by minutes and hours in Google Sheets.

Are there equivalent array formulas to do this?

Yes! You can get that two formulas (hours and minutes) below.

Array Formula to Increment Time in Google Sheets

I hope you could easily understand the above two formulas that increment time (hours and minutes) in Google Sheets. If so, coding the array formulas is quite simple.

Hours Increments in Sheets (Array Formula)

Before entering any expanding array formulas, you must ensure that there are sufficient blank rows (some times columns too) below the formula applied cell.

The following time array formula increments time from 08:00:00 am to 05:00:00 pm (1 hour series like 8 am, 9 am, 10 am … 5 pm).

=ArrayFormula(TIME(row(A8:A17),0,0))

Key this formula in cell A1. Do not copy and paste or drag the fill handle down! In this, the cell reference A8 represents 8 am and A17 represents 5 pm (17 hrs.)

Minutes Increments in Sheets (Array Formula)

Similar to hours, you can use a time array formula to increment minutes too in Google Sheets.

Just enter 08:00:00 am in cell A1. In cell A2, use the below array formula to expand the time up to 5 pm in 30 minutes increments.

=ArrayFormula(TIME(8,row(A1:A18)*30,0))

I am not explaining this formula as you can learn it by looking at the cell reference used and the non-array formula shared above.

That’s all about how to increment the time by minutes and hours in Google Sheets. Enjoy!

Additional Resources:

1. How to Extract Date From Time Stamp in Google Sheets.
2. Google Sheets: The Best Overtime Calculation Formula.
3. How to Convert Military Time in Google Sheets.
4. How to Add Hours, Minutes, Seconds to Time in Google Sheets.
5. Create a Countdown Timer Using Built-in Functions in Google Sheets.
6. How to Format Time to Millisecond Format in Google Sheets.
7. How to Plot a Line Chart Using Lap Times in Milliseconds in Google Sheets.