Filtering on dashboards?

13

Comments

  • I'm sure the answer I'm looking for is answered some place above but it feels like my request is so simple I haven't put my arms around the best way to do this.

    Our PMO team has a Dashboard and with one of the Charts they would like the Dashboard user to be able to filter via fiscal year.

    Thoughts on an option for doing this?



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @tbittickI would suggest creating a form on a separate sheet and attaching the form to the dashboard.

    Then go to the chart data sheet and add a cell that uses a cross sheet reference to pull the most recently "requested" year.

    Reference this cell in all of the formulas that drive the data.


    User submits form on dashboard requesting a specific year. Form entry goes to form sheet. Cross sheet reference on data sheet pulls year and updates calculations appropriately. User refreshes dashboard and is able to see the requested year's data.

  • Thank you so much Paul. Will keep you posted on our success (and or will be coming back hat in hand with more questions!)

    I see from the threads you've really rolled up your sleeves on this one. Thank you for sharing!


    Tony

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi@jwilson10533

    I missed your question.

    Have you solved it?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    Did my post(s) help or 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.

  • Ok. On the filter request sheet, you would set it up to have those three columns plus a system generated Created (date) column. Set the form up to include the three searchable columns and have the form populate new entries at the bottom of the sheet.<\/p>

    On the data sheet, you would create 4 new fields in the Sheet Summary: Row, Project Name, Primary Category, and Other Categories.<\/p>

    In the Sheet Summary Row field, you would enter<\/p>

    =COUNT({Filter Request Sheet Created Column})<\/p>

    This will give you the count of the last row populated (which is the most recent request) and will be used to pull the data from that row to your sheet for the filter.<\/p>

    In the Sheet Summary Project Name field:<\/p>

    =INDEX({Filter Request Sheet Project Name Column}, Row#)<\/p>

    Sheet Summary Primary Category field:<\/p>

    =INDEX({Filter Request Sheet Primary Category Column}, Row#)<\/p>

    Sheet Summary Other Categories field:<\/p>

    =INDEX({Filter Request Sheet Other Categories Column}, Row#)<\/p>

    Then you would create a checkbox type column with the following formula:<\/p>

    =IF(AND(FIND([Project Name]#, [Project Name]@row) > 0, FIND([Primary Category]#, [Primary Category]@row) > 0, FIND([Other Categories]#, [Other Categories]@row) > 0), 1)<\/p>

    Finally you would create and activate a filter that only shows rows that are checked.<\/p>

    Then you can display the Filter Request Form and the sheet you are filtering side by side on a dashboard. The user would then fill out the form and submit it then refresh their browser\/the dashboard.<\/p>

    If you were fine with people access the Sheet Summary fields and updating those themselves, you could skip the step of creating the form and just instruct people to open the sheet summary, enter their search criteria, save the sheet, then refresh the browser. Saves a fair amount of setup and removes the Sheet Summary Row field.<\/p>

    That is going to depend though on whether or not you want people to have direct access to the sheet or not.<\/p>","bodyRaw":"[{\"insert\":\"Ok. On the filter request sheet, you would set it up to have those three columns plus a system generated Created (date) column. Set the form up to include the three searchable columns and have the form populate new entries at the bottom of the sheet.\\n\\nOn the data sheet, you would create 4 new fields in the Sheet Summary: Row, Project Name, Primary Category, and Other Categories.\\nIn the Sheet Summary Row field, you would enter\\n=COUNT({Filter Request Sheet Created Column})\\nThis will give you the count of the last row populated (which is the most recent request) and will be used to pull the data from that row to your sheet for the filter.\\n\\nIn the Sheet Summary Project Name field:\\n=INDEX({Filter Request Sheet Project Name Column}, Row#)\\n\\nSheet Summary Primary Category field:\\n=INDEX({Filter Request Sheet Primary Category Column}, Row#)\\n\\nSheet Summary Other Categories field:\\n=INDEX({Filter Request Sheet Other Categories Column}, Row#)\\n\\nThen you would create a checkbox type column with the following formula:\\n=IF(AND(FIND([Project Name]#, [Project Name]@row) > 0, FIND([Primary Category]#, [Primary Category]@row) > 0, FIND([Other Categories]#, [Other Categories]@row) > 0), 1)\\n\\nFinally you would create and activate a filter that only shows rows that are checked.\\nThen you can display the Filter Request Form and the sheet you are filtering side by side on a dashboard. The user would then fill out the form and submit it then refresh their browser\\\/the dashboard.\\n\\nIf you were fine with people access the Sheet Summary fields and updating those themselves, you could skip the step of creating the form and just instruct people to open the sheet summary, enter their search criteria, save the sheet, then refresh the browser. Saves a fair amount of setup and removes the Sheet Summary Row field.\\n\\nThat is going to depend though on whether or not you want people to have direct access to the sheet or not.\\n\"}]","format":"rich","dateInserted":"2020-03-02T13:34:57+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/241731#Comment_241731","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/241731#Comment_241731

    @Paul NewcomeThanks for these details Paul, I was able to set almost everything up! Just one small detail I can't figure out - how do I link the dashboard to always reference the "checked" row? The sorting doesn't refresh automatically, and even with an active filter my target row number changes.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi@Valerie Legris

    Unfortunately, you'll need to update the Dashboard manually or wait for the automatic update if you're using that.

    Make sense?

    Would that work?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    Did my post(s) help or 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.

  • @Andrée StaråWhen you say update the dashboard, do you mean refresh the browser? If so that's what I did, but it still references the first line of my sheet instead of the "checked" line... Or am I missing something?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Valerie Legris

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or somescreenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])

    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.

  • @Andrée StaråPlease see attached the process I would like to activate, and a description of the issue. I noticed from other posts that this seems to be quite a common need, so I've also raised it as an enhancement suggestion. Thanks for your support!


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Valerie Legris

    I hope you're well and safe!

    Excuse the late response.

    How did it go? Did you get it working?

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    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.

  • firestorm
    firestorm ✭✭
    edited 04/09/21
    Ok. On the filter request sheet, you would set it up to have those three columns plus a system generated Created (date) column. Set the form up to include the three searchable columns and have the form populate new entries at the bottom of the sheet.<\/p>

    On the data sheet, you would create 4 new fields in the Sheet Summary: Row, Project Name, Primary Category, and Other Categories.<\/p>

    In the Sheet Summary Row field, you would enter<\/p>

    =COUNT({Filter Request Sheet Created Column})<\/p>

    This will give you the count of the last row populated (which is the most recent request) and will be used to pull the data from that row to your sheet for the filter.<\/p>

    In the Sheet Summary Project Name field:<\/p>

    =INDEX({Filter Request Sheet Project Name Column}, Row#)<\/p>

    Sheet Summary Primary Category field:<\/p>

    =INDEX({Filter Request Sheet Primary Category Column}, Row#)<\/p>

    Sheet Summary Other Categories field:<\/p>

    =INDEX({Filter Request Sheet Other Categories Column}, Row#)<\/p>

    Then you would create a checkbox type column with the following formula:<\/p>

    =IF(AND(FIND([Project Name]#, [Project Name]@row) > 0, FIND([Primary Category]#, [Primary Category]@row) > 0, FIND([Other Categories]#, [Other Categories]@row) > 0), 1)<\/p>

    Finally you would create and activate a filter that only shows rows that are checked.<\/p>

    Then you can display the Filter Request Form and the sheet you are filtering side by side on a dashboard. The user would then fill out the form and submit it then refresh their browser\/the dashboard.<\/p>

    If you were fine with people access the Sheet Summary fields and updating those themselves, you could skip the step of creating the form and just instruct people to open the sheet summary, enter their search criteria, save the sheet, then refresh the browser. Saves a fair amount of setup and removes the Sheet Summary Row field.<\/p>

    That is going to depend though on whether or not you want people to have direct access to the sheet or not.<\/p>","bodyRaw":"[{\"insert\":\"Ok. On the filter request sheet, you would set it up to have those three columns plus a system generated Created (date) column. Set the form up to include the three searchable columns and have the form populate new entries at the bottom of the sheet.\\n\\nOn the data sheet, you would create 4 new fields in the Sheet Summary: Row, Project Name, Primary Category, and Other Categories.\\nIn the Sheet Summary Row field, you would enter\\n=COUNT({Filter Request Sheet Created Column})\\nThis will give you the count of the last row populated (which is the most recent request) and will be used to pull the data from that row to your sheet for the filter.\\n\\nIn the Sheet Summary Project Name field:\\n=INDEX({Filter Request Sheet Project Name Column}, Row#)\\n\\nSheet Summary Primary Category field:\\n=INDEX({Filter Request Sheet Primary Category Column}, Row#)\\n\\nSheet Summary Other Categories field:\\n=INDEX({Filter Request Sheet Other Categories Column}, Row#)\\n\\nThen you would create a checkbox type column with the following formula:\\n=IF(AND(FIND([Project Name]#, [Project Name]@row) > 0, FIND([Primary Category]#, [Primary Category]@row) > 0, FIND([Other Categories]#, [Other Categories]@row) > 0), 1)\\n\\nFinally you would create and activate a filter that only shows rows that are checked.\\nThen you can display the Filter Request Form and the sheet you are filtering side by side on a dashboard. The user would then fill out the form and submit it then refresh their browser\\\/the dashboard.\\n\\nIf you were fine with people access the Sheet Summary fields and updating those themselves, you could skip the step of creating the form and just instruct people to open the sheet summary, enter their search criteria, save the sheet, then refresh the browser. Saves a fair amount of setup and removes the Sheet Summary Row field.\\n\\nThat is going to depend though on whether or not you want people to have direct access to the sheet or not.\\n\"}]","format":"rich","dateInserted":"2020-03-02T13:34:57+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/241731#Comment_241731","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/241731#Comment_241731

    Hi@Paul Newcome

    How do we handle "searches" for a date range from 1 column? I created "Start" and "End" date fields in the "Search Form" but not sure how to modify the formula for the checkbox.

    To be clearer, I want to show results in a dashboard from a date range.

    Example, I want to see the dashboard with results between Apr 1st to 8th.

    Can you help?


    image.png


    Thanks.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @firestormWhat is your existing formula that you need to add the date criteria into?

  • firestorm
    firestorm ✭✭
    edited 04/11/21

    hello@Paul Newcome

    This is the formula that I have referred to your earlier post:

    =IF(AND(FIND(Member#, [Member]@row) > 0, FIND(Location#, [User's Location(s)]@row) > 0, FIND([Filter Category]#, [Category]@row) > 0), 1)

    This has worked perfectly but I have the "date range" search criteria to be included to return dashboard charts only for that date range. There are 2 fields in the search form - "From" & "To", which are also in the Sheet Summary.

    Another question: Is it possible to FIND multiple search criteria in 1 field? Example, multiple locations to be "searched"?

    image.png

    I tried to change "FIND" to "CONTAINS" but result is not right :(

    =IF(AND(FIND(Member#, [Member]@row) > 0,CONTAINS(Location#, [User's Location(s)]@row), FIND([Filter Category]#, [Category]@row) > 0), 1)

    Thank you.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @firestormTo include the date criteria, you would drop the range/criteria into the AND function.


    =IF(AND(FIND(Member#, [Member]@row) > 0, FIND(Location#, [User's Location(s)]@row) > 0, FIND([Filter Category]#, [Category]@row) > 0, [Date Column]@row>= DATE(2021, 01, 01), [Date Column]@row<= DATE(2021, 01, 31)), 1)


    To search for multiple locations, you would use an OR statement

    OR(CONTAINS("Location 1", [User's Location(s)]@row), CONTAINS("Location 2", [User's Location(s)]@row), CONTAINS("Location 3", [User's Location(s)]@row))


    Then you would use this OR function as one of the "logical statements" of the AND function.

    =IF(AND(FIND(Member#, [Member]@row) > 0,OR(........................)找到([过滤类别]#【类别】@row) > 0, (Date Column]@row>= DATE(2021, 01, 01), [Date Column]@row<= DATE(2021, 01, 31)), 1)


    =IF(AND(FIND(Member#, [Member]@row) > 0,OR(CONTAINS("Location 1", [User's Location(s)]@row), CONTAINS("Location 2", [User's Location(s)]@row), CONTAINS("Location 3", [User's Location(s)]@row))找到([过滤类别]#【类别】@row) > 0, (Date Column]@row>= DATE(2021, 01, 01), [Date Column]@row<= DATE(2021, 01, 31)), 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi, <\/p>

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-26T01:04:51+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-26T01:04:51+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":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/55626/filtering-on-dashboards/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/55626/filtering-on-dashboards/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/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":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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&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":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions