Search code examples
exceldateexcel-formulaincrementautofill

Excel autofill dates but not totally sequentially

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?

Solution

  • 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.

    enter image description here

    enter image description here