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

You could try this as well:

---

• Formula used in cell B5

=LET(
      _nOfDays,E3,
      _timeOfDay,{"Morning","Noon","Night"},
      _todCols,COLUMNS(_timeOfDay),
      _rowsNeeded,_nOfDays*_todCols,
      _firstDate,DATE(D3,B3,C3),
      _dateIncrement,_firstDate+INT(SEQUENCE(_rowsNeeded,,0,1/_todCols)),
      _timeOfDayfill,INDEX(_timeOfDay,MOD(SEQUENCE(_rowsNeeded,,0),_todCols)+1),
      _things,{"x","y","z"},
      _header,HSTACK("Date","Time Of Day","Things"&SEQUENCE(,COLUMNS(_things))),
       IFERROR(VSTACK(_header,
        HSTACK(_dateIncrement,_timeOfDayfill,IFNA(_things,SEQUENCE(ROWS(_timeOfDayfill))))),
       "Please enter the Month,Day,Year and #ofDays in respective cells"))

---

Bit simpler approach without LAMBDA()

---

=LET(
        a,H1,
        b,I1,
        c,SEQUENCE(b*3,,0),
        d,INT(a+c/3),
        e,INDEX({"Morning","Noon","Night"},MOD(c,3)+1),
        f,IFNA({"x","y","z"},SEQUENCE(b*3)),
        HSTACK(d,e,f))

---

OR, inplace of INDEX( ) use SWITCH( )

=LET(
        a,H1,
        b,I1,
        c,SEQUENCE(b*3,,0),
        d,INT(a+c/3),
        e,SWITCH(MOD(c,3)+1,1,"Morning",2,"Noon","Night"),
        f,IFNA({"x","y","z"},SEQUENCE(b*3)),
        HSTACK(d,e,f))

---

Answer 2

=MAKEARRAY(B2*3,5,
    LAMBDA(r,c,
CHOOSE(IF(c>3,3,c),
       INT(B1+(r-1)/3),
       CHOOSE(MOD(r-1,3)+1,"morning","noon","night"),
       CHOOSE(c-2,"x","y","z"))))

Where: B1 is the start date and B2 is the number of different days to list.

Answer 3

Formula Method:

In Cell A1 Enter starting Date

In Cell B1 ="Morning"

In Cell B2 =IF(B1="Morning","Noon",IF(B1="Noon","Night",IF(B1="Night","Morning","Morning")))

In Cell A2 =IF(B2="Morning",A1+1,A1)

Drag to autofill the formulas down.

Answer 4

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

Answer 5

So like this:

IF(COUNTIFS($B$1:B1,"="&B1)=3,B1+1,B1)

Then you can do this for Morning Noon and night:

=IF(COUNTIFS($B$1:B4,"="&B4)=1,"Morning",IF(COUNTIFS($B$1:B4,"="&B4)=2,"Noon",IF(COUNTIFS($B$1:B4,"="&B4)=3,"Night","Error")))

But a simple vlookup() is easier: