Excel autofills dates, not always sequentially

Programming
, , , ,
1

I’m trying to create a simple Excel spreadsheet which tracks three items three times per day (morning, noon, and night) over a three day period.

The table should have the following columns:

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

I am having difficulty getting Excel to autofill the dates and times. Is there a simple formula I am not aware of that could help me?

2

Yes, there is a simple formula you can use in Excel to autofill the dates and times.

To autofill the dates, you can use the following formula in cell A2 and then drag it down to autofill the rest of the dates:

=DATE(2023,6,26) + INT((ROW()-2)/3)

To autofill the times, you can use the following formula in cell B2 and then drag it down to autofill the rest of the times:

=CHOOSE(MOD(ROW()-2,3)+1,"Morning","Noon","Night")

This will generate the desired table with the dates and times filled automatically.