化简公式基于d进入宿舍ate range.
Hello,
I need help simplifying this formula. I need to enter a Quarter based on a date,
For example, if the date is Jan 1 2020, enter Q120 in the cell. If April 1 2020 than enter Q220, and so on.
I am using this formula, which is making the sheet to complicated and possibly impacting the performance.
The crazy formula is:
=IF(AND([Close Date]@row >= DATE(2019, 6, 1), [Close Date]@row <= DATE(2019, 7, 30)), "19-Q3", IF(AND([Close Date]@row >= DATE(2019, 8, 1), [Close Date]@row <= DATE(2019, 10, 31)), "19-Q4", IF(AND([Close Date]@row >= DATE(2019, 11, 1), [Close Date]@row <= DATE(2020, 1, 31)), "20-Q1", IF(AND([Close Date]@row >= DATE(2020, 2, 1), [Close Date]@row <= DATE(2020, 4, 30)), "20-Q2", IF(AND([Close Date]@row >= DATE(2020, 5, 1), [Close Date]@row <= DATE(2020, 7, 31)), "20-Q3", IF(AND([Close Date]@row >= DATE(2020, 8, 1), [Close Date]@row <= DATE(2020, 10, 31)), "20-Q4",IF(AND([Close Date]@row >= DATE(2020, 11, 1), [Close Date]@row <= DATE(2021, 1, 31)), "21-Q1")))))))
How can I use a lookup table of some sort to simplify this. The other issue is that I need to rewrite the formula every 3 months to extend the date range.
Thanks in advance.
Answers
-
Andrée Starå ✭✭✭✭✭✭Hi,
You don't have to use a table if you don't want to. If you start with the latest Quarter and go backward, you need only a simple multiple IF statement as below.
Try something like this.
=IF([Close Date]@row >= DATE(2020; 11; 1); "21-Q1"; IF([Close Date]@row >= DATE(2020; 8; 1); "20-Q4"; IF([Close Date]@row >= DATE(2020; 5; 1); "20-Q3"; IF([Close Date]@row >= DATE(2020; 2; 1); "20-Q2"; IF([Close Date]@row >= DATE(2019; 11; 1); "20-Q1"; IF([Close Date]@row >= DATE(2019; 8; 1); "19-Q4"; IF([Close Date]@row >= DATE(2019; 6; 1); "19-Q3"; "")))))))
The same version but with the below changes for your and others convenience.
=IF([Close Date]@row >= DATE(2020, 11, 1), "21-Q1", IF([Close Date]@row >= DATE(2020, 8, 1), "20-Q4", IF([Close Date]@row >= DATE(2020, 5, 1), "20-Q3", IF([Close Date]@row >= DATE(2020, 2, 1), "20-Q2", IF([Close Date]@row >= DATE(2019, 11, 1), "20-Q1", IF([Close Date]@row >= DATE(2019, 8, 1), "19-Q4", IF([Close Date]@row >= DATE(2019, 6, 1), "19-Q3", "")))))))
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."
Would that work?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
mkilci ✭✭✭✭
Thanks for the reply Andree. I would like to use a table for this formula. Is that possible?
-
Paul Newcome ✭✭✭✭✭✭You would set up your table so that the last day of that quarter is displayed. I suggest setting it up in chronological order just for simplicity when updating, but this will work regardless of the order. Then in the column next to those dates, you would enter the values that you want populated for the quarter ending on that date. I will use Calendar year 2020 as an example.
Then in the column where you want to pull the quarter value, you would use a formula like this:
=INDEX(Index:Index, MATCH(MIN(COLLECT(Match:Match, Match:Match, @cell >= [Date Column]@row)), Match:Match, 0))
Since you are referencing whole columns for the table, you can add/change/delete any values you need on the table without having to update the formula itself.
-
mkilci ✭✭✭✭
Thanks for the reply Paul. I am fairly new to Smartsheets and I am getting the #UNPARSABLE error.
What am I doing wrong? Thanks again
-
Paul Newcome ✭✭✭✭✭✭
Try removing the spaces from the ranges. I also suggest referencing the entire column if you are able so that you do not need to update the ranges in the formula if you extend your table beyond 10 rows.
-
mkilci ✭✭✭✭
Here is what the formula reads (after updating the ranges to column):
INDEX(Index:Index, MATCH(MIN(COLLECT(Match:Match, Match:Match,[Column4]1>=[Date Column]@row)),Match:Match, 0))
What spaces are you referring to? Is it in the formula?
-
Paul Newcome ✭✭✭✭✭✭
In the screenshot you provided, it shows as
Index1 :Index10
instead of
Index1:Index10
It is the same for all of your ranges.
-
Andrée Starå ✭✭✭✭✭✭
Happy to help!
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Morgan Frost ✭✭✭
Hi all, Nice formulas!
I'm trying to do the same thing but with a cross-sheet reference so I can keep the reference table elsewhere. Instead of Quarters I want the sprint name returned. How can I include the cross-sheet references in my formula? Since it's already a range can I tried to plug in the reference instead of the range format or should I be trying to get the colon and the [ ] in there somehow? I tried but it got so messy and seemed redundant--also got errors that way.
I am getting "#incomplete argument set" with this attempt to copy your formula and replace ranges with cross-sheet references:
=INDEX({Sprint Dates Reference (Sprint)}, MATCH(MIN(COLLECT({Sprint Dates Reference(End)}, {Sprint Dates Reference(End)}, @cell >= [Planned Completion]@row), {Sprint Dates Reference(End)}, 0)))
The reference sheet -- all of our sprint cycles (the number with the sprint name is the ID #, so our 20th sprint was April 2019), I am focusing the formula on the End column so I don't miss any weekend items. For example something on 4/26/2019 should show up as "21-May" if my formula worked.
The sheet with the formula in it - I want to display which sprint based on the planned completion date.
Maybe the color-coding will help?
=INDEX(Index:Index, << Ok I get this is supposed to be the column with the value I want returned--the sprint.
MATCH(MIN(COLLECT(Match:Match, << This would be the full range of the reference column dates
Match:Match, << this will be the same thing as the above line I think - need it bc "collect" format
@cell > =[日期列]@row)), < < t这个点o sheet where end user enters date
Match:Match, << no idea what this is doing here
0)) << not sure, something about which column to grab, I may need a 1 or 2?
-
Paul Newcome ✭✭✭✭✭✭
It is just a misplaced closing parenthesis. You need to close out the MIN and COLLECT functions together.
=INDEX({Sprint Dates Reference (Sprint)}, MATCH(MIN(COLLECT({Sprint Dates Reference(End)}, {Sprint Dates Reference(End)}, @cell >= [Planned Completion]@row)), {Sprint Dates Reference(End)}, 0))
-
Morgan Frost ✭✭✭
@Paul NewcomeThank you, that did it! Ugh of course something so simple would trip me up after all that complexity. Love the functionality this gives me.
-
L_123 ✭✭✭✭✭✭
="Q" + INT((MONTH(Date@row) + 2) / 3) + RIGHT(Date@row, 2)
Did this before reading the date of the original question hahah. anyways if someone else is trying to optimize the first question here is a bit shorter solution without a helper table.
-
Katye Reed ✭✭✭✭✭Hello,
我试图完成类似的基础on fiscal year but the results are not returning the correct dates.
=IFERROR(INDEX(Index:Index, MATCH(MIN(COLLECT(Match:Match, Match:Match, @cell >= [SP&T Disposition Date]@row)), Match:Match, 0)), "")
Match Column Index Column
7/1/19 ---- FY 20
7/1/20 ---- FY 21
-
Paul Newcome ✭✭✭✭✭✭
@Katye ReedWhat is it returning if it not returning the correct FY?
-
Katye Reed ✭✭✭✭✭
Please see attached. If i put in June 2020 it is displaying FY 21 instead of FY 20. I'm sure it's something silly but for the life of me I can't figure out what. Thanks so much for the assistance.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+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":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"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":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"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":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+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":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
