Dashboard formula counting rows within date range

bthompson@fa
[email protected] ✭
edited 12/09/19 inFormulas and Functions

First post, Longtime SSheet user but new to formulas and stuck on this last one for a dashboard.

I am trying to count rows where the due date is within a range, the form created date is within 14 days of the due date and the status is not "complete." I have tried a wide variety of solutions.

=COUNTIFS({FAMS Marketing Calendar | 2019 Range 6},@cell>= TODAY(-15), {FAMS Marketing Calendar | 2019 Range 6},@cell< TODAY(15), {FAMS Marketing Calendar | 2019 Range 2},@cell<> "Completed", {FAMS Marketing Calendar | 2019 Range 4}, <{FAMS Marketing Calendar | 2019 Range 1})

Rang 4 is the created date and I have tried removing the time from this also.

我认为我是close and any help would be appreciated.

Tags:
· Share on Facebook Share on Twitter

Comments

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

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

 Watch video
Try removing one of the closing parenthesis after the DATE function.<\/p>"},{"commentID":388644,"body":"

Thanks! That sorted it!😀<\/span><\/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":108438,"type":"question","name":"NETWORKDAYS WITH AN IF??","excerpt":"Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?","snippet":"Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?","categoryID":322,"dateInserted":"2023-08-02T19:37:00+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T20:11:28+00:00","insertUserID":164426,"insertUser":{"userID":164426,"name":"JT_22002","url":"https:\/\/community.smartsheet.com\/profile\/JT_22002","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-03T11:32:53+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-03T12:32:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":18,"score":null,"hot":3382012708,"url":"https:\/\/community.smartsheet.com\/discussion\/108438\/networkdays-with-an-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108438\/networkdays-with-an-if","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108438,"commentID":388600,"name":"Re: NETWORKDAYS WITH AN IF??","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388600#Comment_388600","dateInserted":"2023-08-02T20:11:28+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-03T12:32:35+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-03T11:34:23+00:00","dateAnswered":"2023-08-02T20:11:28+00:00","acceptedAnswers":[{"commentID":388600,"body":"

I'm not sure I understand the goal. If you want to see the days between two column, you will need to already have a date in each of those columns. This seems to contradict stopping the count once one of the columns is populated. Can you provide more detail, or maybe a screenshot\/mockup of what you are looking for?<\/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":108410,"type":"question","name":"Using the \"Created Date\" field in formulas","excerpt":"Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the \"Created Date\" field to all sheets and have an additional field that populates an Invoiced Date. I want a new field that will calculate the number of days between the…","snippet":"Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the \"Created Date\" field…","categoryID":322,"dateInserted":"2023-08-02T13:45:01+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T17:51:15+00:00","insertUserID":141394,"insertUser":{"userID":141394,"name":"KGetchell","url":"https:\/\/community.smartsheet.com\/profile\/KGetchell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T18:07:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"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-02T18:59:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":30,"score":null,"hot":3381985576,"url":"https:\/\/community.smartsheet.com\/discussion\/108410\/using-the-created-date-field-in-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108410\/using-the-created-date-field-in-formulas","format":"Rich","lastPost":{"discussionID":108410,"commentID":388557,"name":"Re: Using the \"Created Date\" field in formulas","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388557#Comment_388557","dateInserted":"2023-08-02T17:51:15+00:00","insertUserID":45516,"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-02T18:59:20+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/C0IGNY6CEA25\/capture-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T09:13:22+00:00","dateAnswered":"2023-08-02T15:09:48+00:00","acceptedAnswers":[{"commentID":388501,"body":"

The Created (date) field is not the issue in this instance. The problem is the data in your Invoiced Date column is actually text strings instead of date values.<\/p>


<\/p>

Try putting this into a date type column and using this new column in place of the Invoiced Date column in your original formula.<\/p>

=DATE(VALUE(MID([Invoiced Date (EP)]@row, FIND(\" \", [Invoiced Date (EP]@row) - 4, 4)), VALUE(LEFT([Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) - 1)), VALUE(MID([Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) + 1, FIND(\"\/\", [Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) + 1) - (FIND(\"\/\", [Invoiced Date (EP)]@row) + 1))))<\/p>"},{"commentID":388539,"body":"

Double check that you are putting it in a date type column. Can you also provide a screenshot of those same rows with the errors but also showing the Invoiced Date? It is working for me.<\/p>


<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/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":[]}],"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

  • Date formula to set cut off period

    I am trying to set a date formula to for the following statement; if the [Next Due Date] is <= 27/07/2023 return "Performed" Example: =IF(ISBLANK([Last inspection]@row), "", IF(DATE(2023,7,27)) < [Next Due Date]@row, "Overdue", "Performed"))

  • NETWORKDAYS WITH AN IF??

    Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?

  • Using the "Created Date" field in formulas

    Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the "Created Date" field to all sheets and have an additional field that populates an Invoiced Date. I want a new field that will calculate the number of days between the…