Get a rare piece of array formula to automatically increment DateTime aka timestamp by one hour in Google Sheets.

Assume cell B1 has the timestamp 10/10/2019 10:00:00 (start time) and B2 has the timestamp 11/10/2019 17:00:00 (end time).

My DateTime increment formula will populate the DateTime in the following sequence.

10/10/2019 10:00:00
10/10/2019 11:00:00
…
11/10/2019 17:00:00

In addition to this, we can slightly modify my formula to increment DateTime by 2 hours or 30 minutes.

To increment DateTime by one hour in Google Sheets, I’ll make use of one of my earlier running sum formula.

Actually there are two types of running sum array formula. One is based on Sumif and the other is based on MMULT.

Here are those two relevant tutorials. If you have time, even though it is not a must, go through these tutorials first.

1. Normal and Array-Based Running Total Formula in Google Sheets.
2. Running Total Array Formula in Excel (Works in Google Sheets also).

We can use MMULT running sum to increment DateTime by one hour/ two hours / thirty minutes as Sumif won’t take other formulas as ‘sum_range’. Confusing, right?

For example;

Syntax:
SUMIF(range, criterion, [sum_range])
Example Formula:
=sumif(B4:B8,"apple",ArrayFormula(row(A1:A5)))

This formula in Google Sheets will return the “Argument must be a range” error as ArrayFormula(row(A1:A5)) is not a physical range but a virtual range.

In our DateTime increment formula, we may require to use a virtual range as the sum_range. So we can’t use Sumif.

Let me start with how to increment DateTime by one hour in Google Sheets.

Formula to Increment DateTime by One Hour in Google Sheets

I’ll help you write the formula step by step. Se let’s begin with the first part of the formula which is finding time duration from two timestamps.

Calculating Time Duration in Hours from Two Timestamps

The first question is why this step is required?

I am using an array formula to automatically increment DateTime by one hour. So this Google Sheets formula is required to calculate the number of rows to expand.

For example, as per the start time and end time in my example, we can calculate the duration in hours as below.

Duration in Hours = (End Time – Start Time) * 24

So the formula to find the duration in hours between the given start time and end time will be as follows.

=(B2-B1)*24

Result: 31 (Hours)

That means there will be 32 rows (31 rows + 1 Start Time row) in our output.

Virtual Helper Column for Running Sum of Time Values

To increment DateTime by one hour in Google Sheets, I am following the below logic (please see the image below).

Pay your attention to C2:C range. I will use MMULT to find the running sum of these time values which will be equal to Timestamp increment by 1 hour.

How to get the time series as above?

First, in cell C2 insert the following Sequence formula which would return the numbers from 1 to 31 in sequential order.

=sequence(31,1)

I hope you know the syntax of SEQUENCE. It’s;

SEQUENCE(rows, [columns], [start], [step])
rows - The number of rows in the function's resulting grid.
columns - The number of columns in the function's resulting grid.

The above formula only uses the first two arguments which are ‘rows’ and ‘columns’.

We can use dynamic row reference in the Sequence formula in cell C2 by using the duration formula as ‘rows’.

=sequence((B2-B1)*24,1)

By modifying this formula as below (use either of the one), we will get 31 rows filled with the number 1 from C2 to C32.

=ArrayFormula(sequence((B2-B1)*24,1)^0)
or
=ArrayFormula(sign(sequence((B2-B1)*24,1)))

We want to increment the DateTime by one hour, right?

So let us convert the above numbers to hours in the format 01:00:00 in each row. How?

We can use the TIME function here.

Syntax: TIME(hour, minute, second)

Use the just above formula as the ‘hour’ argument. Now the C2 formula will look like as below.

=ArrayFormula(time(ArrayFormula(sequence((B2-B1)*24,1)^0),0,0))

Finally, join the start DateTime as the first row of the above output. This will match the output in the range C2:C (please see the image above).

={B1;ArrayFormula(time(ArrayFormula(sequence((B2-B1)*24,1)^0),0,0))}

What is the role of these values in incrementing DateTime by 1 hour in Google Sheets?

The answer is the below running sum of time values.

Making Use of Running Sum to Increment DateTime by One Hour in Google Sheets

I have already mentioned using MMULT as the running sum.

We can use the below MMULT in cell D2 to get the running sum of time values from the range C2:C33 and which will be equal to timestamp/DateTime increment by one hour.

=ArrayFormula(MMULT(IF(ROW(A2:A33)>=TRANSPOSE(ROW(A2:A33))=TRUE,1,0),C2:C33))

Note:

Output must be formatted to date time from the menu – Format > Number.

Replace C2:C33 in the formula with the existing formula in C2.

=ArrayFormula(MMULT(IF(ROW(A2:A33)>=TRANSPOSE(ROW(A2:A33))=TRUE,1,0),{B1;ArrayFormula(time(ArrayFormula(sequence((B2-B1)*24,1)^0),0,0))}))

Now remove the formula in C2 and then cut and paste this new formula from D2 to C2.

We need one more thing to make the above DateTime increment formula flexible. What is that?

We must replace the number 33 in A33 with a dynamic formula. Otherwise, we may face an issue with a different start and end time.

At the beginning of this post, I have mentioned that there will be 32 rows in our output.

That means the 31 returned by duration formula =(B2-B1)*24 and plus one additional row contain the start DateTime.

So we can use =(B2-B1)*24+1 to get the number 32.

Since the array formula to increment DateTime by one hour is in cell C2, I have used A2:A33 (32 numbers from row#2 to row#33) instead of A2:A32 (31 nos from row#2 to row#32) inside of the Row function.

So use =(B2-B1)*24+2 instead of 33 in the A33. We can use Indirect for this.

Note:

1. In simply speaking if you use row(A2, use +2, row(A3, use +3 and so on.
2. We can include INT with the above formula to round the number (down) to an integer as =int((B2-B1)*24+2). This is a must as we are dealing with DateTime and there might be chances of decimal places appearing in the duration calculation output.

Final Formula to Increment DateTime (Timestamp) by One Hour

=ArrayFormula(MMULT(IF(ROW(indirect("A2:A"&int((B2-B1)*24+2)))>=TRANSPOSE(ROW(indirect("A2:A"&int((B2-B1)*24+2))))=TRUE,1,0),{B1;ArrayFormula(time(ArrayFormula(sequence((B2-B1)*24,1)^0),0,0))}))

Note:

More than one ArrayFormula function is not normally required in a combination formula. If you want you can carefully remove them.

I kept it there to make you clearly understand the formula.

I have removed the same in my example sheet shared in the last part of this article.

Can I Change One Hour to N Hours?

Yes. I have limitations (my explanation can cause confusion) to explain it. I will explain to you how to increment DateTime by 2 hours. That may help you a little bit.

Wherever (B2-B1)*24 (duration) appears in the above formula, change that to (B2-B1)*24/2. It decides the total number of rows in the output.

Additionally in the sequence, use +1 like sequence((B2-B1)*24/2,1)^0+1. This will populate the virtual helper column of values like;

10/10/2019 10:00:00
2
2

Earlier it was 1 instead of 2. You can refer to the screenshot above, which is below the subtitle ‘Virtual Helper Column for Running Sum’.

Formula to Increment DateTime (Timestamp) by Two Hours

=ArrayFormula(MMULT(IF(ROW(indirect("A3:A"&int((B2-B1)*24/2+3)))>=TRANSPOSE(ROW(indirect("A3:A"&int((B2-B1)*24/2+3))))=TRUE,1,0),{B1;ArrayFormula(time(ArrayFormula(sequence((B2-B1)*24/2,1)^0+1),0,0))}))

How to Change DateTime Increment from One Hour to 30 Minutes

Take the just above formula. Change the / operator to * operator. I mean change 24/2 to 24*3 (thrice).

Then change this Time formula from;

time(ArrayFormula(sequence((B2-B1)*24*2,1)^0+1),0,0)

To;

time(0,ArrayFormula(sequence((B2-B1)*24*2,1)^0)+29,0)

Formula to Increment DateTime (Timestamp) by 30 Minutes

=ArrayFormula(MMULT(IF(ROW(indirect("A3:A"&int((B2-B1)*24*2+3)))>=TRANSPOSE(ROW(indirect("A3:A"&int((B2-B1)*24*2+3))))=TRUE,1,0),{B1;ArrayFormula(time(0,ArrayFormula(sequence((B2-B1)*24*2,1)^0)+29,0))}))

Example Sheet:

Timestamp Increment

Similar: How to Increment Time By Minutes and Hours in Google Sheets.