Is there a prettier way to write this formula?

RingJake
RingJake ✭✭✭✭✭
edited 06/14/22 inAdd Ons and Integrations

Is there a prettier way to write this formula?

如果(月(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"))))))))))))


另外,如果我想要这个ul的输出timately 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.

如果(月(Finish@row) = ="1", "Q1") AND( JOIN( RIGHT(Finish@row, 2))

Tags:
· Share on Facebook Share on Twitter

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

    · Share on Facebook Share on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    @RingJake

    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)

    · Share on Facebook Share on Twitter
  • RingJake
    RingJake ✭✭✭✭✭

    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".

    · Share on Facebook Share on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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.

    · Share on Facebook Share on Twitter
  • RingJake
    RingJake ✭✭✭✭✭

    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.

    · Share on Facebook Share on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey@RingJake

    Pleaseinclude a screenshot of your actual formulaso I may see thecolored 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?

    · Share on Facebook Share on Twitter
  • RingJake
    RingJake ✭✭✭✭✭

    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.


    Screen Shot 2021-08-12 at 2.19.31 PM.png


    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.

    https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas?source=apphelpicon


    please note I changed the column name from Finish Date to Target Finish Date.

    · Share on Facebook Share on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    @RingJake

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

    · Share on Facebook Share on Twitter
  • RingJake
    RingJake ✭✭✭✭✭

    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?


    Screen Shot 2021-08-12 at 2.48.51 PM.png


    · Share on Facebook Share on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    @RingJake

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

    · Share on Facebook Share on Twitter
  • RingJake
    RingJake ✭✭✭✭✭

    Excellent, thank you@Kelly Moorereally appreciate your help here.

    · Share on Facebook Share on Twitter
  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

    · Share on Facebook Share on Twitter
  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭

    Hi@RingJakeand@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"

    · Share on Facebook Share on Twitter

Categories

Hi @Matthew Drake<\/a> <\/p>

There currently isn't a way to overlay an Outlook Calendar on top of a Smartsheet Calendar; you can do the reverse, publish dates from Smartsheet into Outlook.<\/a><\/p>

Please provide your feedback to the Product team through this form, here.<\/a><\/p>

Thanks,<\/p>

Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":91824,"type":"question","name":"Dynamic View - Dropdown Lists","excerpt":"I have certain single and multiple select list columns that do not have the \"restrict to dropdown values only\" checked on the source sheet but in Dynamic View we are unable to add in our own values if they don't exist in the dropdown. This has been extremely frustrating especially when trying to capture new values and…","snippet":"I have certain single and multiple select list columns that do not have the \"restrict to dropdown values only\" checked on the source sheet but in Dynamic View we are unable to add…","categoryID":343,"dateInserted":"2022-06-06T17:24:20+00:00","dateUpdated":"2022-06-14T14:39:36+00:00","dateLastComment":"2023-08-25T19:03:11+00:00","insertUserID":113932,"insertUser":{"userID":113932,"name":"Emmy Anderson","url":"https:\/\/community.smartsheet.com\/profile\/Emmy%20Anderson","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jplUI_CI3bw!8o9VN1d3-no!6TkM7ozjpN6","dateLastActive":"2023-05-04T14:55:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":91566,"lastUserID":156788,"lastUser":{"userID":156788,"name":"Chris Broughton","url":"https:\/\/community.smartsheet.com\/profile\/Chris%20Broughton","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p0HeqRqVoXw!8_nRN9ijwLw!lUie_n6pld_","dateLastActive":"2023-08-25T18:59:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":566,"score":null,"hot":3347530651,"url":"https:\/\/community.smartsheet.com\/discussion\/91824\/dynamic-view-dropdown-lists","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/91824\/dynamic-view-dropdown-lists","format":"Rich","tagIDs":[319],"lastPost":{"discussionID":91824,"commentID":392652,"name":"Re: Dynamic View - Dropdown Lists","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392652#Comment_392652","dateInserted":"2023-08-25T19:03:11+00:00","insertUserID":156788,"insertUser":{"userID":156788,"name":"Chris Broughton","url":"https:\/\/community.smartsheet.com\/profile\/Chris%20Broughton","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p0HeqRqVoXw!8_nRN9ijwLw!lUie_n6pld_","dateLastActive":"2023-08-25T18:59:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-06-08T12:36:40+00:00","dateAnswered":"2022-06-06T18:38:56+00:00","acceptedAnswers":[{"commentID":332119,"body":"

Hi @Emmy Anderson<\/a> <\/p>

You are not missing anything. This is how DV works with dropdowns unfortunately. My work around in the past is to create an other option in the drop down, user chooses save. Then have logic built for a text field that appears so the user can input the values not present in dropdown. <\/p>

Hope this helps<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":319,"urlcode":"functionality","name":"functionality"}]},{"discussionID":66594,"type":"question","name":"Is there a way to use OR in a Dynamic View filter condition?","excerpt":"In a View, I can create a custom filter that selects where the Project Lead is \"Mike\" but I want to also say \"OR Debbie.\" Is that possible? It looks like I can only choose AND.","snippet":"In a View, I can create a custom filter that selects where the Project Lead is \"Mike\" but I want to also say \"OR Debbie.\" Is that possible? It looks like I can only choose AND.","categoryID":343,"dateInserted":"2020-03-09T22:27:57+00:00","dateUpdated":"2022-06-14T14:36:49+00:00","dateLastComment":"2023-08-23T08:56:50+00:00","insertUserID":34501,"insertUser":{"userID":34501,"name":"MikeChambers","url":"https:\/\/community.smartsheet.com\/profile\/MikeChambers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/CT5AV825MQ3E\/nG12D47VYRO8A.jpg","dateLastActive":"2023-03-05T01:19:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":91566,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":438,"score":null,"hot":3276579287,"url":"https:\/\/community.smartsheet.com\/discussion\/66594\/is-there-a-way-to-use-or-in-a-dynamic-view-filter-condition","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/66594\/is-there-a-way-to-use-or-in-a-dynamic-view-filter-condition","format":"rich","lastPost":{"discussionID":66594,"commentID":392055,"name":"Re: Is there a way to use OR in a Dynamic View filter condition?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392055#Comment_392055","dateInserted":"2023-08-23T08:56:50+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/814\/KBHVAAPJ5RYA.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"OR.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-03-12T17:06:46+00:00","dateAnswered":"2020-03-12T13:01:18+00:00","acceptedAnswers":[{"commentID":242817,"body":"

You are more than welcome!<\/p>

Glad to hear that it worked for your present use-case. <\/p>

✅Remember! <\/strong>Did my post help or answer your question or solve your problem? Please help the Community by <\/em>marking it as the accepted answer\/helpful<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=343&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-dateLastComment&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":643,"limit":3},"title":"Trending Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts

  • Outlook Calendar events populating Smartsheet?

    Good morning, I have attempted to search the answer but not having much luck. My question is: The team I am apart of currently use Outlook calendars to record the various event sessions taking place throughout the day in different rooms. We have begun using Smartsheet to record the events so we can display the statistical…

  • Dynamic View - Dropdown Lists

    I have certain single and multiple select list columns that do not have the "restrict to dropdown values only" checked on the source sheet but in Dynamic View we are unable to add in our own values if they don't exist in the dropdown. This has been extremely frustrating especially when trying to capture new values and…

  • Is there a way to use OR in a Dynamic View filter condition?

    In a View, I can create a custom filter that selects where the Project Lead is "Mike" but I want to also say "OR Debbie." Is that possible? It looks like I can only choose AND.

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video