=DATE() won't increment DAY when I try to drag or fill the other cells

Question

I'm working on an excel file to track attendance. The excel sheet should have the flexibility of dynamically changing the year of dates. The user can enter the current year in a cell and it should update the year on all existing dates.

Let's say we have inputted year 2022 in cell O17. All the dates in every month should change to year 2022. From January - December.

I am close to finalizing the formula.

My formula for January 2022 is =DATE($O17,MONTH(1),DAY(1))

However, when I drag the formula in one cell to another to fill it, the day won't increment. Using the formula above, when I drag it, it just puts January 1, 2022 in all cells. I can't get a January 2, 2022, January 3, 2022.... an so on.

I'd appreciate any suggestions.

Thank you.

Answer 1

Change the formula to:

=DATE($O$17,MONTH(COLUMN(A1)),DAY((ROW(A1))))

Answer 2

Because Date in Excel is represented as a number of days from a particular date (1. 1. 1900 = 1), you can use this approach (I find it more readable).

Notice, rows 10-39 are hidden. dd.mm.rrrr format is used here.

If you see weird numbers, just format cells as date. (They might be formatted automatically.)

In case of more complex formulas, keep leap year in the mind. Did you know that year 1900 was not a leap one and 2100 will not be too?