Do you want to automatically increment DateTime (also known as timestamp) values by one hour in Google Sheets? Well, I have a rare piece of array formula that can help you achieve that. With this formula, you can easily populate your DateTime values in a sequence, making your work more efficient and saving you time.
Calculating Time Duration in Hours from Two Timestamps
Before we dive into the formula, let’s understand why we need to calculate the time duration in hours between two timestamps. This step is essential because we’ll be using an array formula to increment the DateTime by one hour. The time duration will help us determine the number of rows we need to expand.
To calculate the duration in hours, you can use the formula:
=(End Time - Start Time) * 24
For example, if the duration between the start time and end time is 31 hours, you will need 32 rows in your output (31 rows plus 1 start time row).
Virtual Helper Column for Running Sum of Time Values
To increment DateTime by one hour in Google Sheets, we will be using the MMULT function to find the running sum of time values. This running sum will be equal to the timestamp increment by one hour.
To create a time series for the running sum, follow these steps:
-
In cell C2, insert the following Sequence formula:
=sequence((B2-B1)*24,1)This formula will return the numbers from 1 to 31 in sequential order. You can modify it by using the duration formula (
(B2-B1)*24) as the “rows” argument. -
Convert the numbers to hours in the format
01:00:00by using the TIME function:
=ArrayFormula(time(ArrayFormula(sequence((B2-B1)*24,1)^0),0,0)) -
Join the start DateTime as the first row of the output using the following formula:
={B1;ArrayFormula(time(ArrayFormula(sequence((B2-B1)*24,1)^0),0,0))}
These values in the virtual helper column (range C2:C) will serve as the basis for incrementing the DateTime by one hour.
Making Use of Running Sum to Increment DateTime by One Hour
Now that we have our virtual helper column, we can utilize the MMULT function to calculate the running sum of time values. This running sum will be equivalent to incrementing the DateTime values by one hour.
Simply use the following formula in cell D2:
=ArrayFormula(MMULT(IF(ROW(A2:A33)>=TRANSPOSE(ROW(A2:A33))=TRUE,1,0),C2:C33))
Make sure to format the output as a date and time from the “Format” menu.
Can I Change One Hour to N Hours?
Yes, you can! If you want to increment DateTime by 2 hours, simply modify the duration calculation in the formula. Wherever (B2-B1)*24 appears, change it to (B2-B1)*24/2. This will determine the total number of rows in the output.
Additionally, adjust the sequence formula to include +1, like sequence((B2-B1)*24/2,1)^0+1. This will populate the virtual helper column with values starting from 2 instead of 1.
Here’s the formula to increment DateTime 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))}))
You can apply a similar approach to change the DateTime increment to any other interval, such as 30 minutes. Just adjust the calculations accordingly.
Example Sheet
To help you understand these formulas better, I’ve prepared an example sheet demonstrating how to increment DateTime values. You can find the sheet here.
Don’t let the complexity of DateTime calculations in Google Sheets hold you back. With these array formulas, you can easily increment DateTime values by any desired interval, saving you time and effort.
Happy Spreadsheet-ing!
Similar: How to Increment Time By Minutes and Hours in Google Sheets
