IF Function to Add Quarter and Year

Jarret.Birdwell

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.

Jarret.Birdwell

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.