Subtracting 1 month from TODAY, previous year error

=DATE(YEAR(day13), MONTH(day13) - 1, DAY(day13))

I am using the above formula to subtract one month from today's date for a rolling 12 month report. However, the formula errors above 15/01/22 (I expected it to revert to 15/12/21) is there a way I can build the formula so it does not error and continues into the previous year?

Find more posts tagged with

Formulas

Accepted answers

All comments

Jeff Reisman

@Dayle_Taylor You'll need to add an IF statement in there to introduce a different formula when the month is January.

=IF(MONTH(Day@row) = 1, DATE((YEAR(Day@row) - 1), 12, DAY(Day@row), DATE(YEAR(Day@row), MONTH(day@row) - 1, DAY(day13)))

In English - If the Month is January, set the date using this year minus 1, month = 12, day = this day, otherwise, set the date using this year, this month minus 1, this day.

Dayle_Taylor

@Jeff Reisman thanks for your prompt reply, yes I think an IF function is the way to do it, but as the bottom row of my table showing 15/09/22 is calculated using =TODAY() then the all date columns should change by +1 every day. Not sure if your formula would be compatible with that? I.e. The Day5 cell will change to February in 15 days.

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.