公式

TODAY(), \"Green\") 3) if %complete is full and finish date…","categoryID":319,"dateInserted":"2017-05-12T15:12:37+00:00","dateUpdated":null,"dateLastComment":"2018-04-23T15:12:40+00:00","insertUserID":14976,"insertUser":{"userID":14976,"name":"Jawanza Hadley","url":"https:\/\/community.smartsheet.com\/profile\/Jawanza%20Hadley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2017-11\/2d\/08\/n2d083c99ee208833d8ee158468f6baf8.jpg","dateLastActive":"2019-08-28T11:20:15+00:00","banned":0,"punished":0,"private":false,"label":""},"updateUserID":null,"lastUserID":45711,"lastUser":{"userID":45711,"name":"Jeff Ford","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Ford","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-11-06T14:28:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":true,"sink":false,"countComments":10,"countViews":66,"score":null,"hot":10,"url":"https:\/\/community.smartsheet.com\/discussion\/9256\/help-with-nested-if-formulat","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/9256\/help-with-nested-if-formulat","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":319,"name":"Archived 2017 Posts","url":"https:\/\/community.smartsheet.com\/categories\/c2017","allowedDiscussionTypes":["discussion","question"]},"tags":[{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":228,"urlcode":"Project Management","name":"Project Management"},{"tagID":234,"urlcode":"Health-care","name":"Healthcare"},{"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":23666,"type":"discussion","name":"VLOOKUP and Conditional Formatting","excerpt":"I updated our smartsheet team schedule to grab dates from my department's master schedule. However, the conditional formatting we set up that visually shows us when a project is nearing or past its due date no longer works on the VLOOKUP cells Is there something I'm doing wrong? Or is this a limitation of the software? Has…","categoryID":322,"dateInserted":"2018-04-16T09:56:54+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-23T14:53:50+00:00","insertUserID":44921,"insertUser":{"userID":44921,"name":"joconnor","url":"https:\/\/community.smartsheet.com\/profile\/joconnor","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-04-23T14:11:15+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\/518\/n0UL39EAWX965.jpg","dateLastActive":"2022-12-20T16:47:13+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":115,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/23666\/vlookup-and-conditional-formatting","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/23666\/vlookup-and-conditional-formatting","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":213,"urlcode":"Conditional Formatting","name":"Conditional Formatting"},{"tagID":228,"urlcode":"Project Management","name":"Project Management"},{"tagID":231,"urlcode":"Cell linking","name":"Cell linking"},{"tagID":241,"urlcode":"Calendar","name":"Calendar View"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":270,"urlcode":"Technology","name":"Technology"},{"tagID":299,"urlcode":"Card View","name":"Card View"}],"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":24031,"type":"discussion","name":"% as Negative","excerpt":"How do you structure your formula calculate negative if it is over 100% For example, I am at 144% which is over budget Current Formula =AC1 \/ [Project Budget]1 Results 144%, I would like it to calculate -44% once I am over budget. ","categoryID":322,"dateInserted":"2018-04-20T17:53:43+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-23T11:36:38+00:00","insertUserID":41966,"insertUser":{"userID":41966,"name":"Tony Barthelemy","url":"https:\/\/community.smartsheet.com\/profile\/Tony%20Barthelemy","photoUrl":"https:\/\/lh3.googleusercontent.com\/-XdUIqdMkCWA\/AAAAAAAAAAI\/AAAAAAAAAAA\/4252rscbv5M\/photo.jpg","dateLastActive":"2021-02-21T20:22:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":41966,"lastUser":{"userID":41966,"name":"Tony Barthelemy","url":"https:\/\/community.smartsheet.com\/profile\/Tony%20Barthelemy","photoUrl":"https:\/\/lh3.googleusercontent.com\/-XdUIqdMkCWA\/AAAAAAAAAAI\/AAAAAAAAAAA\/4252rscbv5M\/photo.jpg","dateLastActive":"2021-02-21T20:22:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":102,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/24031\/as-negative","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/24031\/as-negative","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":23996,"type":"discussion","name":"Nesting SUBSTITUTE functions","excerpt":"Hello All, I need help!!! I am trying to write a formula that replaces the letter \"a\" with the number \"1\", \"b\" with \"2\", \"c\" with \"3\", etc.... The individual formula of (ex.) =SUBSTITUTE([Column3]1, \"a\", \"1\") is easy enough, but for whatever reason I can't seem to figure out how to nest it with the rest of the letters and…","categoryID":322,"dateInserted":"2018-04-20T10:26:43+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-23T08:04:28+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":"2022-12-22T21:35:53+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":"2022-12-22T21:35:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":227,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/23996\/nesting-substitute-functions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/23996\/nesting-substitute-functions","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":209,"urlcode":"Media & Entertainment","name":"Media & Entertainment"},{"tagID":216,"urlcode":"Non-profit","name":"Non-profit"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":263,"urlcode":"Government","name":"Government"},{"tagID":281,"urlcode":"Support","name":"Support"}],"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":23836,"type":"discussion","name":"Counting Unique Values based on a date criteria","excerpt":"I am using this formula to count for unique values within a range of cell. I want to be able to use a criteria, a date for the range of cell, what should the formula look like? =SUM((IF(FREQUENCY(MATCH(B2:B16,B2:B16,0),MATCH(B2:B16,B2:B16,0))>0,1)))","categoryID":322,"dateInserted":"2018-04-18T09:55:06+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-22T21:27:56+00:00","insertUserID":42091,"insertUser":{"userID":42091,"name":"ODT","url":"https:\/\/community.smartsheet.com\/profile\/ODT","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-03\/58\/75\/n5875e0f99afbea49ab1b2f57a7bf2f6d.jpg","dateLastActive":"2019-05-09T23:43:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":42091,"lastUser":{"userID":42091,"name":"ODT","url":"https:\/\/community.smartsheet.com\/profile\/ODT","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-03\/58\/75\/n5875e0f99afbea49ab1b2f57a7bf2f6d.jpg","dateLastActive":"2019-05-09T23:43:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":1950,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/23836\/counting-unique-values-based-on-a-date-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/23836\/counting-unique-values-based-on-a-date-criteria","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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">