Creating a Perpetual Quarterly and Yearly Count

Hello All!

Any thoughts or help would be appreciated. I am trying to use my Month Renewal Date to create my Quarterly Renewal Date. However, my formula is not providing an out put.

=IF([Month Renewal Due]@row = "November", "Q4", IF([Month Renewal Due]@row = "January", "Q1", IF([Month Renewal Due]@row = "February", "Q1", IF([Month Renewal Due]@row = "March", "Q1", IF([Month Renewal Due]@row = "April", "Q2", IF([Month Renewal Due]@row = "May", "Q2", IF([Month Renewal Due]@row = "June", "Q2", IF([Month Renewal Due]@row = "July", "Q3", IF([Month Renewal Due]@row = "August", "Q3", IF([Month Renewal Due]@row = "September", "Q3", IF([Month Renewal Due]@row = "October", "Q4", IF([Month Renewal Due]@row = "December", "Q4" + " " + YEAR([Month Renewal Due]@row)))))))))))))

Find more posts tagged with

Formulas

dates

Accepted answers

All comments

AKnight

Hi @ssuser01!

The first problem you are encountering is because you need to use the CONTAINS function. The cell you are referencing has December in it, but also 2024. Your second problem is the last portion of your formula. I don't know if your "month renewal due" column is in a date column and so the YEAR function won't work. I provided a fixed formula below and included the OR function to help clean it up:

=IF(OR(CONTAINS("January", [Month Renewal Due]:[Month Renewal Due]), CONTAINS("February", [Month Renewal Due]:[Month Renewal Due]), CONTAINS("March", [Month Renewal Due]:[Month Renewal Due])), "Q1", IF(OR(CONTAINS("April", [Month Renewal Due]:[Month Renewal Due]), CONTAINS("May", [Month Renewal Due]:[Month Renewal Due]), CONTAINS("June", [Month Renewal Due]:[Month Renewal Due])), "Q2", IF(OR(CONTAINS("July", [Month Renewal Due]:[Month Renewal Due]), CONTAINS("August", [Month Renewal Due]:[Month Renewal Due]), CONTAINS("September", [Month Renewal Due]:[Month Renewal Due])), "Q3", IF(OR(CONTAINS("October", [Month Renewal Due]:[Month Renewal Due]), CONTAINS("November", [Month Renewal Due]:[Month Renewal Due]), CONTAINS("December", [Month Renewal Due]:[Month Renewal Due])), "Q4")))) + " " + YEAR([Next Renewal 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.