@courtney.w<\/a> You need to put the Cancelled portion of the formula first.<\/p>=IF( [Ideal Completion Date]@row = \"Cancelled\", 0, IF(AND([% Complete]@row < 1, [Ideal Completion Date]@row < TODAY(), NOT(ISBLANK([Ideal Completion Date]@row))), 1, IF(COUNTIF(DESCENDANTS([At Risk]@row), =1) > 0, 1, 0)))<\/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":111778,"type":"question","name":"Modified Date Column Issues","excerpt":"I am pulling the modified date and converting it to a value between 0000 and 2359 when a checkbox is selected. My issue is that, many times a user with a different time-zone setting will come into the sheet and when they do that, it automatically modifies ALL of the columns (even if they're locked). Is it possible to stop…","snippet":"I am pulling the modified date and converting it to a value between 0000 and 2359 when a checkbox is selected. My issue is that, many times a user with a different time-zone…","categoryID":322,"dateInserted":"2023-10-17T17:01:34+00:00","dateUpdated":null,"dateLastComment":"2023-10-17T20:47:04+00:00","insertUserID":161632,"insertUser":{"userID":161632,"name":"SteyJ","title":"Jacob Stey","url":"https:\/\/community.smartsheet.com\/profile\/SteyJ","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5Zjq-V_rc4o!zmkrczO71bo!6kwXWfi9FUH","dateLastActive":"2023-10-17T22:58:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":161632,"lastUser":{"userID":161632,"name":"SteyJ","title":"Jacob Stey","url":"https:\/\/community.smartsheet.com\/profile\/SteyJ","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5Zjq-V_rc4o!zmkrczO71bo!6kwXWfi9FUH","dateLastActive":"2023-10-17T22:58:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":33,"score":null,"hot":3395138918,"url":"https:\/\/community.smartsheet.com\/discussion\/111778\/modified-date-column-issues","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111778\/modified-date-column-issues","format":"Rich","lastPost":{"discussionID":111778,"commentID":400426,"name":"Re: Modified Date Column Issues","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400426#Comment_400426","dateInserted":"2023-10-17T20:47:04+00:00","insertUserID":161632,"insertUser":{"userID":161632,"name":"SteyJ","title":"Jacob Stey","url":"https:\/\/community.smartsheet.com\/profile\/SteyJ","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5Zjq-V_rc4o!zmkrczO71bo!6kwXWfi9FUH","dateLastActive":"2023-10-17T22:58:30+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-10-17T20:47:39+00:00","dateAnswered":"2023-10-17T18:24:46+00:00","acceptedAnswers":[{"commentID":400413,"body":"
You will need to insert a text\/number column and use<\/p>
=[Modified Date]@orw + \"//www.santa-greenland.com/community/discussion/78462/\"<\/p>
plus quote quote<\/p>
To turn it into a text value. You would then use a copy row automation set to trigger at whatever point you want to capture this. It will then be static data in the second sheet where you can use a formula with cross sheet references to pull the static date\/time stamp back over into your working sheet.<\/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":111779,"type":"question","name":"12-month mark and 18-month mark formula","excerpt":"I have a column that has staff's date of hire. I have built columns for their 12-month mark and their 18-month mark but I am stumped on the formula to pull the date for the 12-month and 18-month mark. Can someone help!","snippet":"I have a column that has staff's date of hire. I have built columns for their 12-month mark and their 18-month mark but I am stumped on the formula to pull the date for the…","categoryID":322,"dateInserted":"2023-10-17T17:16:17+00:00","dateUpdated":null,"dateLastComment":"2023-10-17T18:09:10+00:00","insertUserID":166841,"insertUser":{"userID":166841,"name":"Erica Rochelle Smith","title":"Regulatory and Quality Assurance Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Erica%20Rochelle%20Smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-17T18:47:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":15741,"lastUser":{"userID":15741,"name":"ker9","url":"https:\/\/community.smartsheet.com\/profile\/ker9","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-17T23:22:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":31,"score":null,"hot":3395130927,"url":"https:\/\/community.smartsheet.com\/discussion\/111779\/12-month-mark-and-18-month-mark-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111779\/12-month-mark-and-18-month-mark-formula","format":"Rich","lastPost":{"discussionID":111779,"commentID":400402,"name":"Re: 12-month mark and 18-month mark formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400402#Comment_400402","dateInserted":"2023-10-17T18:09:10+00:00","insertUserID":15741,"insertUser":{"userID":15741,"name":"ker9","url":"https:\/\/community.smartsheet.com\/profile\/ker9","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-17T23:22:19+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\/O19F4E6J3JZ3\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/O19F4E6J3JZ3\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/O19F4E6J3JZ3\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/O19F4E6J3JZ3\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/O19F4E6J3JZ3\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/O19F4E6J3JZ3\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-17T17:31:04+00:00","dateAnswered":"2023-10-17T17:26:38+00:00","acceptedAnswers":[{"commentID":400393,"body":"
Hi @Erica Rochelle Smith<\/a> <\/p>One option is to add days to the Hire date:<\/p>
12 months: =[Date Hired]@row + 365<\/p>
18 months: =[Date Hired]@row + 547.5<\/p>
Hope this helps!<\/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
How can I unflag tasks that have a status of 'Cancelled'? I am looking to add a new condition to my At Risk column formula so that cancelled tasks are not flagged. My current formula flags all tasks that are past the completion date and are 100% complete. I want to keep it so that the ideal completion date and % complete is still visible so I still have that data, but I do not…
Modified Date Column Issues I am pulling the modified date and converting it to a value between 0000 and 2359 when a checkbox is selected. My issue is that, many times a user with a different time-zone setting will come into the sheet and when they do that, it automatically modifies ALL of the columns (even if they're locked). Is it possible to stop…
12-month mark and 18-month mark formula I have a column that has staff's date of hire. I have built columns for their 12-month mark and their 18-month mark but I am stumped on the formula to pull the date for the 12-month and 18-month mark. Can someone help!
Paul Newcome ✭✭✭✭✭✭
