Using IF, AND, OR
Looking for formula advice/help! I would like to write a formula that will:
- Mark Past Due if Complete By is greater than today + task is not complete
- If no Complete By date exists, than Past Due flag remains unchecked
So far, I've been able to create a flag based on the first bullet, however-- tasks that do not have a Complete By date associated show as flagged. Would I use an OR in this situation? Not even sure if this is possible-- Any thoughts?
Best Answers
-
@SPark ✭✭
I've figured it out! I tried to go back and remove my question but it won't let me delete.Here is what I used..
=IF(ISBLANK([Complete By]@row), 0, IF(AND([Complete By]@row < TODAY(), [Task Complete]@row = 0), 1))
-
Genevieve P. Employee AdminHi@SPark
So glad to hear your figured it out! Thanks for posting your resolution - this could be useful to others so we'll leave your post up, if that's alright with you.
Cheers,
Genevieve
Answers
-
@SPark ✭✭
I've figured it out! I tried to go back and remove my question but it won't let me delete.Here is what I used..
=IF(ISBLANK([Complete By]@row), 0, IF(AND([Complete By]@row < TODAY(), [Task Complete]@row = 0), 1))
-
Genevieve P. Employee Admin
Hi@SPark
So glad to hear your figured it out! Thanks for posting your resolution - this could be useful to others so we'll leave your post up, if that's alright with you.
Cheers,
Genevieve
Help Article Resources
Categories
=[Target End Date]1<\/p>
with this:<\/p>
=Index([Target End Date]:[Target End Date], 1)<\/p>
the second value in an index function is the row—usually we make this a MATCH lookup function but it can also be a number.<\/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":107112,"type":"question","name":"How to we exclude a specific name from a DISTINCT LIST USER formula","excerpt":"I would like to exclude a specific user name from this formula. The formula is working fine, and it is returning a distinct list of user names. However, we need to exclude certain users, that are no longer in the group. For example, how do I say to exclude user \"Joe Black\" from this list? =IFERROR(INDEX(DISTINCT({Trial…","snippet":"I would like to exclude a specific user name from this formula. The formula is working fine, and it is returning a distinct list of user names. However, we need to exclude certain…","categoryID":322,"dateInserted":"2023-06-29T22:30:22+00:00","dateUpdated":"2023-06-29T22:33:31+00:00","dateLastComment":"2023-06-30T13:51:12+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":157974,"lastUserID":157974,"lastUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":66,"score":null,"hot":3376213894,"url":"https:\/\/community.smartsheet.com\/discussion\/107112\/how-to-we-exclude-a-specific-name-from-a-distinct-list-user-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107112\/how-to-we-exclude-a-specific-name-from-a-distinct-list-user-formula","format":"Rich","lastPost":{"discussionID":107112,"commentID":383323,"name":"Re: How to we exclude a specific name from a DISTINCT LIST USER formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383323#Comment_383323","dateInserted":"2023-06-30T13:51:12+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T13:43:49+00:00","dateAnswered":"2023-06-30T12:01:20+00:00","acceptedAnswers":[{"commentID":383296,"body":"
Hi @Filippo<\/a> <\/p>
