Is there a prettier way to write this formula?
Is there a prettier way to write this formula?
=IF(MONTH(Finish@row) = "1", "Q1", IF(MONTH(Finish@row) = "2", "Q1", IF(MONTH(Finish@row) = "3", "Q1", IF(MONTH(Finish@row) = "4", "Q2", IF(MONTH(Finish@row) = "5", "Q2", IF(MONTH(Finish@row) = "6", "Q2", IF(MONTH(Finish@row) = "7", "Q3", IF(MONTH(Finish@row) = "8", "Q3", IF(MONTH(Finish@row) = "9", "Q3", IF(MONTH(Finish@row) = "10", "Q4", IF(MONTH(Finish@row) = "11", "Q4", IF(MONTH(Finish@row) = "12", "Q4"))))))))))))
Additionally, if I wanted the output of this to ultimately say "Q1 2021", pulling the 2021 from the RIGHT function of my "Finish" date cell would it be possible to add this? I've been playing around with something like this, but can't quite get it.
=IF(MONTH(Finish@row) = "1", "Q1") AND( JOIN( RIGHT(Finish@row, 2))
Answers
-
Hey @RingJake
Try this
=IF(MONTH(Finish@row)<=3, "Q1 "+YEAR(Finish@row), IF(MONTH(Finish@row)<=6, "Q2 "+YEAR(Finish@row), IF(MONTH(Finish@row)<=9, "Q3 "+YEAR(Finish@row), IF(MONTH(Finish@row)<=12, "Q4 "+YEAR(Finish@row))))
Note there is one spaces following the Q# to provide a break between the Q# and 2021. If you want more/less space, adjust this within the quote marks.
cheers
cheers
-
Oh, and if you wanted your Year function to be only two digits, substitute this for the Year functions above
RIGHT(YEAR(Finish@row),2)
-
Hi Kelly,
Thanks for the reply the less than or equal to portion makes sense, however I still cannot get the second part joining the year text into the output. Did this work for you?
I've tried breaking it apart to only =RIGHT(YEAR(Finish@row),2) as written above, but am getting "UNPARSABLE".
-
Hey @RingJake
Interesting. Have you tried breaking it apart further to =YEAR(Finish@row)?
This formula works in my test sheet
=IF(MONTH(Finish@row) <= 3, "Q1 " + RIGHT(YEAR(Finish@row), 2), IF(MONTH(Finish@row) <= 6, "Q2 " + RIGHT(YEAR(Finish@row), 2), IF(MONTH(Finish@row) <= 9, "Q3 " + RIGHT(YEAR(Finish@row), 2), IF(MONTH(Finish@row) <= 12, "Q4 " + RIGHT(YEAR(Finish@row), 2)))))
If you take a screenshot of your formula, I may be able to help troubleshoot. An Unparseable generally refers to an error with commas or parentheses. The colored text in a screenshot will help me find the problem.
-
Hi @Kelly Moore ,
Thanks for the reply, I've tried this a few ways including to dissect it into smaller pieces and still cannot output a working response. I am being told "UNPARSABLE" for the function below.
Do you think you can try breaking it down into smaller pieces first?
=IF(MONTH([Target Finish Date]@row) = "1", "Q1" + RIGHT(YEAR(Finish@row), 2))
Also, the shorter version with the quarters doesn't quite work, I think it is because the quarters need to be between two numbers since the higher you go will always be greater than its previous quarter.
-
Hey @RingJake
Please include a screenshot of your actual formula so I may see the colored text. Please use my formula for the screenshot.
I see that in the formula above, the Month numbers are enclosed in quotes. Please remove. Quotes around numbers indicate that the value will be used as text. You cannot do greater than, less than evaluations on text.
The quarters should work. An IF statement works to find the first True value in the equation - then it stops. So if the Month value is 3, the evaluation of the IFs stops at the first term. It does not proceed to the 6 or 9 or 12 terms. Thus when writing an IF statement, one must always be aware of the order of the IF statements.
How is your Finish date being entered? Is it a manual entry of a date, or is it calculated? If calculated, what is that calculation?
-
Hey Kelly,
The date is entered manually, and formatted in the proper "Date" column type. As you can see I've tried removing the quotes around the month but am still not having any luck.
The other thing about this formulas is it is a "Column Formula" so certain formats may be incompatible based off this webpage, however it doesn't appear there should be a problem here.
please note I changed the column name from Finish Date to Target Finish Date.
-
If your column name contains a space, number or special character, the column name must be enclosed in square brackets. You can tell from the formula that the column names are not what smartsheet is expecting since the column names are not multi colored.
=IF(MONTH([Target Finish Date]@row) <= 3, "Q1 " + RIGHT(YEAR(([Target Finish Date]@row), 2), IF(MONTH(([Target Finish Date]@row) <= 6, "Q2 " + RIGHT(YEAR(([Target Finish Date]@row), 2), IF(MONTH(([Target Finish Date]@row) <= 9, "Q3 " + RIGHT(YEAR(([Target Finish Date]@row), 2), IF(MONTH(([Target Finish Date]@row) <= 12, "Q4 " + RIGHT(YEAR(([Target Finish Date]@row), 2)))))
-
Hey @Kelly Moore,
Thanks for the reply. This formula seems to be working and it taking the correct information, however the cell remains blank, could this be a bug?
-
My bad. As I did copy and paste of the formulas I must have copied an extra parenthesis that carried over with every YEAR function.
Please copy paste this directly in your sheet
=IF(MONTH([Target Finish Date]@row) <= 3, "Q1 " + RIGHT(YEAR([Target Finish Date]@row), 2), IF(MONTH([Target Finish Date]@row) <= 6, "Q2 " + RIGHT(YEAR([Target Finish Date]@row), 2), IF(MONTH([Target Finish Date]@row) <= 9, "Q3 " + RIGHT(YEAR([Target Finish Date]@row), 2), IF(MONTH([Target Finish Date]@row) <= 12, "Q4 " + RIGHT(YEAR([Target Finish Date]@row), 2)))))
-
Excellent, thank you @Kelly Moore really appreciate your help here.
-
Sorry for the confusion before, that was my bad. As a rough rule of thumb - not always but a good guide, the number of final parentheses will match the number of IFs in your equation. Nested functions, like our RIGHT(YEAR) may impact the final count, but counting your IFs is a good rule of thumb to know. It wasn't until I looked at the colored parentheses that it made me question why I had inserted so many in the formula. Just an fyi to help you with trouble shooting formulas in the future.
Thanks for the question to the community - every question will someday eventually help someone else out.
cheers,
Kelly
-
Hi @RingJake and @Kelly Moore,
You could also try this...
=IFERROR("Q" + IF(MONTH([Target Finish Date]@row) > 9, 4, IF(MONTH([Target Finish Date]@row) > 6, 3, IF(MONTH([Target Finish Date]@row) > 3, 2, 1))) + " " + RIGHT(YEAR([Target Finish Date]@row), 2), "")
For February 1 2021...
="Q1 21"
