Creating a Perpetual Quarterly and Yearly Count

ssuser01
ssuser01 ✭✭
edited 11/22/24 in Formulas and Functions

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)))))))))))))

Tags:

Answers

  • AKnight
    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)

    Let's connect!

    www.linkedin.com/in/ashleylknight

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!