IF Function to Add Quarter and Year

Jarret.Birdwell
edited 10/11/23 in Formulas and Functions

Hello,

I am editing a smartsheet that tracks tasks. I have a column for the quarter that needs the year based off [End Date]@row for all tasks added after the calculated quarter.

I am using the formula:

=IF(ISDATE([End Date]1), IF(MONTH([End Date]1) <= 3, "Q1", IF(MONTH([End Date]1) <= 6, "Q2", IF(MONTH([End Date]1) <= 9, "Q3", "Q4"))))

How can I add something that references YEAR([End Date]1) after the "Q#" in Smartsheet without having to make several "Reference" columns?

Ex.: Q2 2024

I am not the owner of the sheet and don't have high permission to add columns.

Answers

  • Jarret.Birdwell
    edited 10/11/23

    All,

    I figured it out. You can do text and references/formulas in the result of a cell using "+". For this question I would need to change the true result of each IF statement from "Q#" to "#Q " + YEAR([End Date]1). I was then requested to lower the Year down to the last two digits; I am able to do that using the RIGHT function: RIGHT(YEAR([End Date]1),2).

    The final formula now looks like:

    =IF(ISDATE([End Date]1), IF(MONTH([End Date]1) <= 3, "1Q " + RIGHT(YEAR([End Date]1), 2), IF(MONTH([End Date]1) <= 6, "2Q " + RIGHT(YEAR([End Date]1), 2), IF(MONTH([End Date]1) <= 9, "3Q " + RIGHT(YEAR([End Date]1), 2), "4Q " + RIGHT(YEAR([End Date]1), 2)))))

    And gives results like: 2Q 24

    Hopefully this saves someone time attempting to do the same or similar functions.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!