How to create a conditional formula with date specifications?

Adrienne Buesa
Adrienne Buesa ✭
inFormulas and Functions

How do I create a formula that counts the number of rows containing [STATUS] “Green” and have [DATE] = Today and [DATE] = Last Seven Days from Today. I do not want to capture rows that are older than seven days from today.

Tags:
· Share on Facebook Share on Twitter

Answers

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
Hi @BristolCVN<\/a> <\/p>

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

You must add\/change the Rule #1 name.<\/p>

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

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/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":443,"urlcode":"dynamic-view","name":"Dynamic View"}]},{"discussionID":108756,"type":"question","name":"Showing what quarters students are here from date ranges","excerpt":"Hello - I am trying to build a report for my supervisor that shows what quarters students are here from two date ranges. My columns are Experience Start Date Experience End date Quarter - I have this formula in quarter currently that pulls the quarter from whatever month the experience start date begins in =\"Q\" +…","snippet":"Hello - I am trying to build a report for my supervisor that shows what quarters students are here from two date ranges. My columns are Experience Start Date Experience End date…","categoryID":322,"dateInserted":"2023-08-09T19:29:46+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T16:56:30+00:00","insertUserID":164879,"insertUser":{"userID":164879,"name":"Taylorbecker1123","title":"Education Admin Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Taylorbecker1123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T16:53:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164879,"lastUser":{"userID":164879,"name":"Taylorbecker1123","title":"Education Admin Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Taylorbecker1123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T16:53:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3383297176,"url":"https:\/\/community.smartsheet.com\/discussion\/108756\/showing-what-quarters-students-are-here-from-date-ranges","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108756\/showing-what-quarters-students-are-here-from-date-ranges","format":"Rich","lastPost":{"discussionID":108756,"commentID":390066,"name":"Re: Showing what quarters students are here from date ranges","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390066#Comment_390066","dateInserted":"2023-08-10T16:56:30+00:00","insertUserID":164879,"insertUser":{"userID":164879,"name":"Taylorbecker1123","title":"Education Admin Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Taylorbecker1123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T16:53:37+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\/MS2Q5K5FZJPC\/smartsheet-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Smartsheet.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T16:56:13+00:00","dateAnswered":"2023-08-09T21:34:54+00:00","acceptedAnswers":[{"commentID":389920,"body":"

Try this:<\/p>

=\"Q\" + ROUNDUP(MONTH([EXPERIENCE START DATE:]@row) \/ 3) + IF(ROUNDUP(MONTH([EXPERIENCE START DATE:]@row) \/ 3) <> ROUNDUP(MONTH([EXPERIENCE END DATE:]@row) \/ 3), \" - Q\" + ROUNDUP(MONTH([EXPERIENCE END DATE:]@row) \/ 3), \"//www.santa-greenland.com/community/discussion/67511/\")<\/p>

Hope that works for ya!<\/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":108765,"type":"question","name":"Production Downtime Tracking - Crossing the Dateline","excerpt":"Thanks for taking the time to read this post. I am attempting to restructure my companies end-of-shift reporting system with smartsheet to facilitate better automated data processing. An issue i am currently dealing with is how to calculate the impact or \"Yield\" in minutes of line downtime as we cross from one day to…","snippet":"Thanks for taking the time to read this post. I am attempting to restructure my companies end-of-shift reporting system with smartsheet to facilitate better automated data…","categoryID":322,"dateInserted":"2023-08-09T21:31:26+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T13:35:07+00:00","insertUserID":148642,"insertUser":{"userID":148642,"name":"Isaac T.","url":"https:\/\/community.smartsheet.com\/profile\/Isaac%20T.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T13:46:22+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-10T17:09:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3383292993,"url":"https:\/\/community.smartsheet.com\/discussion\/108765\/production-downtime-tracking-crossing-the-dateline","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108765\/production-downtime-tracking-crossing-the-dateline","format":"Rich","lastPost":{"discussionID":108765,"commentID":390007,"name":"Re: Production Downtime Tracking - Crossing the Dateline","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390007#Comment_390007","dateInserted":"2023-08-10T13:35:07+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-10T17:09: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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T13:47:12+00:00","dateAnswered":"2023-08-10T10:14:42+00:00","acceptedAnswers":[{"commentID":389977,"body":"

Hi @Isaac T.<\/a> <\/p>

=([End HR]@row + IF([End HR]@row < [Start HR]@row, 12, 0)<\/strong>) * 60 + [End Min]@row - ([Start HR]@row * 60 + [Start Min]@row)<\/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

  • Can't Save Field Logic Updates in Dynamic View

    Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It asks the user to select a Category and to deter using Issues/Miscellaneous, I have added a dropdown column in the sheet for the user to confirm…

  • Showing what quarters students are here from date ranges

    Hello - I am trying to build a report for my supervisor that shows what quarters students are here from two date ranges. My columns are Experience Start Date Experience End date Quarter - I have this formula in quarter currently that pulls the quarter from whatever month the experience start date begins in ="Q" +…

  • Production Downtime Tracking - Crossing the Dateline

    Thanks for taking the time to read this post. I am attempting to restructure my companies end-of-shift reporting system with smartsheet to facilitate better automated data processing. An issue i am currently dealing with is how to calculate the impact or "Yield" in minutes of line downtime as we cross from one day to…