Repeat a range of a number of times

Question

I'm trying to repeat something like this for every day of the year. It could start at anytime, for example, like below:

01/01/2020 06:00:00
01/01/2020 07:00:00
.... .... ... Then...
01/01/2020 20:00:00
02/01/2020 06:00:00
02/01/2020 07:00:00
.... .... ... Then...
02/01/2020 20:00:00
03/01/2020 06:00:00
03/01/2020 07:00:00

I tried the following link, but this only worked on texts. Repeat a range a number of times

Another one I've tried is this, but I cannot set the start/end time: Repeat date sequence in Excel

Any help would be greatly appreciated!

Answer 1

The below formula takes a SEQUENCE of every single hour, then filters it down to only the time ranges you need (in this case between TIME(6,0,0) and TIME(20,0,0), so 6:00 to 20:00).

=LET(
AllHours, SEQUENCE(365*24, 1, DATE(2022, 1, 1), TIME(1,0,0)),
IsCorrectTime, BYROW(AllHours,LAMBDA(row,AND(MOD(row, 1)>=TIME(6,0,0),MOD(row,1)<=TIME(20,0,0)))),
FILTER(AllHours, IsCorrectTime)
)