Adding 1 month to date over multiple years - errors

Natalia Kataoka

Hello,

I have a sheet in which I need a formula to advance a date by 1 month, over multiple years. I have used the below formula, which works to advance within the same year, and move from Dec to Jan, but every month after the new year has somehow changed the day in my date.

=IFERROR(DATE(YEAR([Jan '24 RCVD]@row), MONTH([Jan '24 RCVD]@row) + 1, DAY([Jan '24 RCVD]@row)), DATE(YEAR([Jan '24 RCVD]@row) + 1, 1, DAY([Jan '24 RCVD]@row)))

See screen shot, below. I want the day of the month to be the same in each cell, I cannot figure out why the day changed from 30 to 2, and February was skipped.

Any ideas?

MedaUser

Hi @Natalia Kataoka,

It seems the reason your February month jumped into March is due to February only have 28/29 days depending on the year. I would imagine your formula would work if your date(s) were for the 1st of the month. If you can't make these the 1st of the month, then you may be stuck manually updating the February date for each year unfortunately.