Formulas

[Stop Date]1, 0, 1) Stop date is a date field with =TODAY() - 15 and Date is any given date. The idea is to check items where Date is in the last two weeks other wise don't check it. It does not work. If I enter a date newer than TODAY()-15 ir unchecks. If the date is older it…","categoryID":322,"dateInserted":"2018-05-16T12:48:16+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-16T15:22:27+00:00","insertUserID":5578,"insertUser":{"userID":5578,"name":"Bill Brandt","url":"https:\/\/community.smartsheet.com\/profile\/Bill%20Brandt","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-06-11T20:42:00+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":11072,"lastUser":{"userID":11072,"name":"Mike Wilday","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Wilday","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/749H48MC3045\/nF9FQYPTX0X7D.jpg","dateLastActive":"2023-08-02T19:49:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":111,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/25346\/trouble-with-date-math","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/25346\/trouble-with-date-math","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":25316,"type":"discussion","name":"COUNTIF Date is in the next 180 days from today","excerpt":"I'm trying to use the COUNTIF function to search a date column to count how many dates are within the next 180 days from today...but my formulas aren't working correctly. Failed Attempt 1: =COUNTIF([Renewal Date]2, >=TODAY(+180)) Failed Attempt 2: =COUNTIF([Renewal Date]2, >=TODAY()180) Failed Attempt 3: =COUNTIF([Renewal…","categoryID":322,"dateInserted":"2018-05-15T16:21:08+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-15T19:02:23+00:00","insertUserID":3680,"insertUser":{"userID":3680,"name":"Darcie Wilson","url":"https:\/\/community.smartsheet.com\/profile\/Darcie%20Wilson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-05-15T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":11072,"lastUser":{"userID":11072,"name":"Mike Wilday","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Wilday","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/749H48MC3045\/nF9FQYPTX0X7D.jpg","dateLastActive":"2023-08-02T19:49:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":910,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/25316\/countif-date-is-in-the-next-180-days-from-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/25316\/countif-date-is-in-the-next-180-days-from-today","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":4051,"type":"discussion","name":"Calculate days remaining","excerpt":"I have an estimated project Finish date of 8\/9\/16 in a column \"Finish\". this date is in row 6 and row 161. I have entered a row name TODAY and set the date to current date in the 'Finish\" column row 4. I attempted to use the formula: =NETDAYS([Finish]6,[Finish]4) I get no result or error message. Just the formula. What am…","categoryID":318,"dateInserted":"2016-01-28T09:57:14+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-15T12:01:52+00:00","insertUserID":5258,"insertUser":{"userID":5258,"name":"Michael Lang","url":"https:\/\/community.smartsheet.com\/profile\/Michael%20Lang","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2016-01-28T09:42:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":5651,"lastUser":{"userID":5651,"name":"Stan Adell","url":"https:\/\/community.smartsheet.com\/profile\/Stan%20Adell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/85\/ad\/n85ad0d191e91807aa433dec2c6e10aa3.jpg","dateLastActive":"2020-11-13T19:09:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":true,"sink":false,"countComments":2,"countViews":193,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/4051\/calculate-days-remaining","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/4051\/calculate-days-remaining","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/02\/fd\/02fdb99764d0bfa08df74d91803a9b29.JPG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Date calculation issue.JPG"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":318,"name":"Archived 2016 Posts","url":"https:\/\/community.smartsheet.com\/categories\/c2016","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":263,"urlcode":"Government","name":"Government"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":549,"urlcode":"account-and-user-management","name":"Account and User Management"}],"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

条件格式为所有孩子吗?
Hi, we principally use smartsheets to generate Gantt charts for our various projects. Ideally we would like to set up conditional formatting (for example task-bar color) for all of the children of different parents. So for example, let's say "Phase 1" of a project is the parent, we would like an easy way to have the…
How do I create an IF formula with checkbox?
Hi there! I'm trying to create a formula using a checkbox and I'm really struggling. Using the parameters below, can anyone help me with the formula? Column 1 = Australian states/jurisdictions (SA, WA, etc) Column 2 = Complete column with checkbox PROBLEM: If Column 2 is checked in Row 1, I want Column 1 in Row 2 to…
COUNTIFS returning #INCORRECT ARGUMENT SET
Hi all - I cannot figure out what's going wrong here. I'm trying to count rows in another sheet ("Sheet A") where both the following are true: * Done = 0 * IsChild = 0 My formula is: * =COUNTIFS({Sheet A Range 2}, 0, {Sheet A Range 4}, 0) Now this works perfectly! So in a cell right below that one, I then try to count rows…
Automated Alert & Action Issues
Hello I am trying to solve an issue with a system I have set up for Automated Alerts & Actions. See screenshots below. As you can see by screenshot 3, I have created a trigger column based on date surpassed; once that date is surpassed the cell will change from N to Y. The idea ultimately is when it changes to Y it…
sumifs with 2 dates and a rolling 12 month forecast
Hi Community, I'm now thinking about this already too long, so maybe you can help me out? It's about controlling the total amount of guarantees active within the next 12 months (rolling forecast). Every guarantee has a value, a starting date and a termination date. Setting within 1 sheet: Column1 = Euro guaranteed…
Help converting and excel formula to smartsheet
Hi everyone, I'm pretty new to smartsheet so I may be asking your help for something that is quite easy to do :) I have an excel formula that I need to migrate to smartsheet to help me transform seconds in HH:MM:SS The excel formula is =TEXT(IF(I4="";50;I4)/(24*60*60);"hh:mm:ss") & ":00" Is there a way to use the same…
how can i auto populate in smartsheets
I am trying to auto populate a column based on a selection I make in another column.
Trouble with Date Math
I have this formula in a checkbox =IF(Date1 > [Stop Date]1, 0, 1) Stop date is a date field with =TODAY() - 15 and Date is any given date. The idea is to check items where Date is in the last two weeks other wise don't check it. It does not work. If I enter a date newer than TODAY()-15 ir unchecks. If the date is older it…
COUNTIF Date is in the next 180 days from today
I'm trying to use the COUNTIF function to search a date column to count how many dates are within the next 180 days from today...but my formulas aren't working correctly. Failed Attempt 1: =COUNTIF([Renewal Date]2, >=TODAY(+180)) Failed Attempt 2: =COUNTIF([Renewal Date]2, >=TODAY()180) Failed Attempt 3: =COUNTIF([Renewal…
Calculate days remaining
I have an estimated project Finish date of 8/9/16 in a column "Finish". this date is in row 6 and row 161. I have entered a row name TODAY and set the date to current date in the 'Finish" column row 4. I attempted to use the formula: =NETDAYS([Finish]6,[Finish]4) I get no result or error message. Just the formula. What am…

Formulas

Discussion List