Adding 1 month to date over multiple years - errors

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?

Find more posts tagged with

Formulas

dates

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.