Formula for 1st of the month in a prior month is coming up Invalid Value

I have 3 different formulas for 1st of the month 1 month, 2 months and 3 months before renewal date, excluding holidays and weekends. The formula is working great, until it has to calculate back in a previous year.

1st Follow-Up Email Due Formula: =WORKDAY(DATE(YEAR([Renewal Date]@row), MONTH([Renewal Date]@row) - 2, 1) - 1, 1, Holidays:Holidays)

For 01/01/2026 renewal date this should produce 10/01/2025, but it gives an error message of "Invalid Value"

I've noticed that the ones that produce "Invalid Value" should be displaying a previous years date for the output date, in relation to the Renewal Date. Is there a way to fix this?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

I use an IFERROR around my DATE function to output a different DATE function.

=WORKDAY(IFERROR(DATE(YEAR([Renewal Date]@row), MONTH([Renewal Date]@row) - 2, 1), DATE(YEAR([Renewal Date]@row) - 1, MONTH([Renewal Date]@row) + 10, 1)) - 1, 1, Holidays:Holidays)

Paul Newcome

@John_Foster The key to the above is that absolute values of the numbers added/subtracted in both of the month functions must add up to 12 and you flip the sign.

So going back two months is -2. That means the second one is +10. 2+10 = 12 and you flip the sign. Going back three months would be -3 and +9. Going forward 8 months would be +8 and -4. So on and so forth (and don't forget to adjust the year in the second one either).

This only works for less than 12 months in either direction. 12 months would be just a +/- 1 year with no month adjustment. Beyond 12 months in either direction requires a much more complicated formula. Excel uses EDATE. SS doesn't have that function, but I was able to write a formula for it. You should be able to locate it if you search for EDATE here in the Community.

All comments

John_Foster

Hi @Kasey Mabary,

I had a similar issue in one of my sheets and I added a nested if statement that selected a specific month if the date I was checking has month value 1, 2 or 3.

The other option you could use, and I have also used this, is a second sheet which has a lookup table with the various months based on the renewal date.

I would be happy to talk through either in more detail if it would help.

Paul Newcome

I use an IFERROR around my DATE function to output a different DATE function.

=WORKDAY(IFERROR(DATE(YEAR([Renewal Date]@row), MONTH([Renewal Date]@row) - 2, 1), DATE(YEAR([Renewal Date]@row) - 1, MONTH([Renewal Date]@row) + 10, 1)) - 1, 1, Holidays:Holidays)

John_Foster

@Paul Newcome that is a very clever solution to the problem, I am going to go back through and review my formulas based on that now.

Thanks,
John

Paul Newcome

@John_Foster The key to the above is that absolute values of the numbers added/subtracted in both of the month functions must add up to 12 and you flip the sign.

John_Foster

Thanks Paul, the additional information will be of use in some future projects I have and have already amended a couple of sheets that were using a way more complex formula.

Paul Newcome

@John_Foster Happy to help. 👍️

Paul Newcome

For anyone that may find it helpful, here is my most recent version of an EDATE replica that works for both positive and negative number of months:

=DATE(YEAR([Original Date]@row) + (FLOOR(MONTH([Original Date]@row) + ([# of Months]@row - 1), 12) / 12), IF(MOD(MONTH([Original Date]@row) + [# of Months]@row, 12) = 0, 12, MOD(MONTH([Original Date]@row) + [# of Months]@row, 12)) + IF(IF(MOD(MONTH([Original Date]@row) + [# of Months]@row , 12) = 0, 12, MOD(MONTH([Original Date]@row) + [# of Months]@row, 12)) < 0, 12, 0), DAY([Original Date]@row))

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.