Formulas and Functions

=COUNT(DISTINCT(COLLECT({Range 6}, {Range 3}, @cell = 2023)))<\/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":107723,"type":"question","name":"IF OR Function for more than two fields or values","excerpt":"Dear all, I have a main task with several (3 or 4) sub tasks and want the top tasks' status to be changed, as soon as one of the sub tasks' status is being changed. The following formula works, in the field of the main task, for only two sub tasks =IF(OR(Status11 = \"In Progress\", Status12 = \"In Progress\"), \"In Progress\",…","snippet":"Dear all, I have a main task with several (3 or 4) sub tasks and want the top tasks' status to be changed, as soon as one of the sub tasks' status is being changed. The following…","categoryID":322,"dateInserted":"2023-07-18T12:11:01+00:00","dateUpdated":null,"dateLastComment":"2023-07-18T13:38:32+00:00","insertUserID":161446,"insertUser":{"userID":161446,"name":"MarkFX","url":"https:\/\/community.smartsheet.com\/profile\/MarkFX","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-18T15:35:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161446,"lastUser":{"userID":161446,"name":"MarkFX","url":"https:\/\/community.smartsheet.com\/profile\/MarkFX","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-18T15:35:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":34,"score":null,"hot":3379370973,"url":"https:\/\/community.smartsheet.com\/discussion\/107723\/if-or-function-for-more-than-two-fields-or-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107723\/if-or-function-for-more-than-two-fields-or-values","format":"Rich","lastPost":{"discussionID":107723,"commentID":385742,"name":"Re: IF OR Function for more than two fields or values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385742#Comment_385742","dateInserted":"2023-07-18T13:38:32+00:00","insertUserID":161446,"insertUser":{"userID":161446,"name":"MarkFX","url":"https:\/\/community.smartsheet.com\/profile\/MarkFX","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-18T15:35: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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-18T13:56:27+00:00","dateAnswered":"2023-07-18T12:29:27+00:00","acceptedAnswers":[{"commentID":385719,"body":"

Are the sub-tasks setup as children of the main task? If so, this should work:<\/p>

=IF(CONTAINS(\"In Progress\", CHILDREN()), \"In Progress\", \"Not Started\")<\/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":107725,"type":"question","name":"Date Generation formula #Invalid Value error, sometimes.","excerpt":"Good morning, evening and afternoon all! I have a formula that I am using to generate a Warranty End Date based off of an Order Date =DATE(YEAR([Order Date | Warranty Start Date]@row) + 1, MONTH([Order Date | Warranty Start Date]@row) + 6, DAY([Order Date | Warranty Start Date]@row)) This seems to only work for me when the…","snippet":"Good morning, evening and afternoon all! I have a formula that I am using to generate a Warranty End Date based off of an Order Date =DATE(YEAR([Order Date | Warranty Start…","categoryID":322,"dateInserted":"2023-07-18T12:42:09+00:00","dateUpdated":null,"dateLastComment":"2023-07-18T13:06:41+00:00","insertUserID":146935,"insertUser":{"userID":146935,"name":"Coen","url":"https:\/\/community.smartsheet.com\/profile\/Coen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-18T13:25:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":146935,"lastUser":{"userID":146935,"name":"Coen","url":"https:\/\/community.smartsheet.com\/profile\/Coen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-18T13:25:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":35,"score":null,"hot":3379370930,"url":"https:\/\/community.smartsheet.com\/discussion\/107725\/date-generation-formula-invalid-value-error-sometimes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107725\/date-generation-formula-invalid-value-error-sometimes","format":"Rich","lastPost":{"discussionID":107725,"commentID":385736,"name":"Re: Date Generation formula #Invalid Value error, sometimes.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385736#Comment_385736","dateInserted":"2023-07-18T13:06:41+00:00","insertUserID":146935,"insertUser":{"userID":146935,"name":"Coen","url":"https:\/\/community.smartsheet.com\/profile\/Coen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-18T13:25:38+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-07-18T13:06:44+00:00","dateAnswered":"2023-07-18T13:02:14+00:00","acceptedAnswers":[{"commentID":385733,"body":"

Your current formula is not accounting for months wrapping into the next year. If you have a start date with months 7-12, the result will be 13-18, which are invalid months. It looks like your warranty period is 18 months? This should work:<\/p>

=IF(MONTH([Order Date | Warranty Start Date]@row) > 6, DATE(YEAR([Order Date | Warranty Start Date]@row) + 2, MONTH([Order Date | Warranty Start Date]@row) - 6, DAY([Order Date | Warranty Start Date]@row)), DATE(YEAR([Order Date | Warranty Start Date]@row) + 1, MONTH([Order Date | Warranty Start Date]@row) + 6, DAY([Order Date | Warranty Start Date]@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":[]}">