Exceljet

Quick, clean, and to the point

Sequence of times

Excel formula: Sequence of times
Generic formula 
=TIME(SEQUENCE(n,1,HOUR(start),1),0,0)
Explanation 

To generate a sequence of times, you can use the SEQUENCE function, in combination with functions like TIME, HOUR, MINUTE, and SECOND. Or you can use SEQUENCE with raw numeric values that represent time. In the example shown, the formula in D5 is:

=TIME(SEQUENCE(12,1,HOUR(B5),1),0,0)

which generates a series of 12 times, beginning at 7:00 AM, the date in B5.

How this formula works

The SEQUENCE function is a dynamic array function that can generate multiple results. When used by itself on the worksheet, SEQUENCE outputs an array of results that "spill" onto the worksheet in a "spill range".

In the example shown, we want to generate 12 times, one hour apart, starting at 7:00 AM (the value in B5). To do this, we use the TIME function, which can create a valid Excel time with hours, minutes, and seconds given as decimal values.

To create the numbers used for hours with SEQUENCE, we have:

SEQUENCE(12,1,HOUR(B5))

The HOUR function is used to convert the time in B5 to a decimal value for hours (7). So the function resolves to:

SEQUENCE(12,1,7)

which generates an array of numbers like this:

{7;8;9;10;11;12;13;14;15;16;17;18}

This array is returned to the TIME function as the hour argument:

=TIME({7;8;9;10;11;12;13;14;15;16;17;18},1),0,0)

The TIME function returns 12 times to a spill range beginning in cell D5.

With raw numbers

The example above used the TIME function for convenience, but it is also possible to work with numeric values directly. Since Excel time is recorded as fractions of a day, the formula above can be written like this:

=SEQUENCE(12,1,B5,1/24)
This example uses "Dynamic Arrays", a beta feature in Excel currently available through the Office Insiders program. Expected release in 2019.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.