Excel autofill dates but not totally sequentially

Question

I'm trying to create a simple excel spreadsheet which tracks some things 3 times per day (morning, noon, and night). The table should look like this:

Date	Time of Day	Thing 1	Thing 2	Thing 3
26/06/2023	Morning	x	y	z
26/06/2023	Noon	x	y	z
26/06/2023	Night	x	y	z
27/06/2023	Morning	x	y	z
27/06/2023	Noon	x	y	z
27/06/2023	Night	x	y	z
28/06/2023	Morning	x	y	z
28/06/2023	Noon	x	y	z
28/06/2023	Night	x	y	z

Thought this would be simple but can't get Excel to autofill in that way with dates (3 x same, and then increment).

Maybe there's a simple formula I'm not thinking of?

Answer 1

Create Table Data

• The date column needs to be formatted manually.

• Possibly (probably) replace the inputs with cell references.
  For Events and Things use one cell per value.

=LET(FirstDate,DATEVALUE("26-6-2023"),dDays,5,
    Events,VSTACK("Morning","Noon","Night"),
    Things,HSTACK("x","y","z"),
    ec,TOCOL(Events),tr,TOROW(Things),
    eRows,ROWS(ec),totRows,eRows*dDays,tCols,COLUMNS(tr),
    d,FirstDate+INT(SEQUENCE(totRows,,0)/eRows),
    e,INDEX(ec,MOD(SEQUENCE(totRows,,0),eRows)+1),
    t,INDEX(tr,SEQUENCE(totRows,,1,0),SEQUENCE(,tCols)),
HSTACK(d,e,t))

• If you want to keep the values hardcoded, this could be a better approach:

=LET(FirstDate,DATEVALUE("26-6-2023"),dDays,5,
    Events,"Morning,Noon,Night",Things,"x,y,z",
    ec,TOCOL(TEXTSPLIT(Events,",")),tr,TOROW(TEXTSPLIT(Things,",")),
    eRows,ROWS(ec),totRows,eRows*dDays,tCols,COLUMNS(tr),
    d,FirstDate+INT(SEQUENCE(totRows,,0)/eRows),
    e,INDEX(ec,MOD(SEQUENCE(totRows,,0),eRows)+1),
    t,INDEX(tr,SEQUENCE(totRows,,1,0),SEQUENCE(,tCols)),
HSTACK(d,e,t))