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!

Sorry, I just edited it! It is one hour incremental from 6am to 8pm @Harun24hr — Sophia, Mar 22 '23 at 13:10
So start on `01/01/2020 06:00:00` then increment 1 hour? But how it is `01/01/2020 20:00:00`? Explain in brief in post. — Harun24hr, Mar 22 '23 at 13:14
@Harun24hr 8pm = 20:00:00. OP wants 6am-8pm, so their desired results is appropriate. — JNevill, Mar 22 '23 at 13:17

score 4 · Answer 1 · answered Mar 22 '23 at 14:12

4

If you got Excel 365 use @SpencerBarnes with no doubt, but in case you got and older version of Excel you may do:

A1 = 01/01/2020 06:00:00

A2 = =IF(HOUR(A1)=20;INT(A1)+1+1/4;A1+1/24)

The main disavantadge of this solution is that you need manually to drag until last day of year... so if you can use @SpencerBarnes use it.

answered Mar 22 '23 at 14:12

Foxfire And Burns And Burns

10,665
2
17
38

Thank you! I get the "There's a problem with this formula." error when I put the A2 formula in. What am I doing wrong? – Sophia Mar 23 '23 at 08:14
@Sophia My argument separator is the semicolon `;` probably your argument separator is the comma `,` – Foxfire And Burns And Burns Mar 23 '23 at 10:15

score 3 · Answer 2 · answered Mar 22 '23 at 13:17

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)
)

VBasic2008 · Answer 3 · 2023-03-22T15:32:12.753

1

Repeat/Increment Date Time In Column

=LET(Data,A2:A4,Repeats,C1,Start,C2,
    rs,ROWS(Data),s,Start-1,Seq,SEQUENCE(rs*Repeats-s,,s),
INDEX(Data,MOD(Seq,rs)+1)+INT(Seq/rs))

edited Mar 22 '23 at 15:32

answered Mar 22 '23 at 15:04

VBasic2008

44,888
5
17
28

Repeat a range of a number of times

3 Answers3

Repeat/Increment Date Time In Column