My formula doesn't work when I choose the first of the month as Effective Date

this is my formula:

=IFERROR(DATE(YEAR([Effective Date]@row) + ROUNDDOWN((MONTH([Effective Date]@row) + ([Contract Term (Months)]@row)) / 12, 0) + IF(IF(MOD(MONTH([Effective Date]@row) + ([Contract Term (Months)]@row), 12) = 0, 12, MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12)) = 12, -1), IF(MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12) = 0, 12, MOD(MONTH([Effective Date]@row - 1) + ([Contract Term (Months)]@row), 12)), DAY([Effective Date]@row) - 1), "")

it works except when I choose the first of the month. Please help.

Find more posts tagged with

Formulas

Accepted answers

All comments

Nick Korna

Hi @WinaHath,

What result are you expecting if you put the 1st of the month in the effective date?

WinaHath

it is supposed to take the number in the contract term column, add that to the effective date, minus 1 day to output the End Date

Nick Korna

Using your above data as a sample, is this correct (albeit in different date format)?

New formula:

The 1st of the month is failing in your formula as you can't have the 0th of the month (from DAY([Effective Date]@row) - 1) ), so the MAX portion of the formula fixes by making any 0s into 1s.

Let me know if I'm barking up the wrong tree with this or if you've any problems/questions.

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.