If and date functions; sorting rows by priority
Hi,
I am attempting to create a task priority column that is using the Harvey Balls (red, yellow, green and gray).
I am wanting these to automatically change to:
1. Gray - if the date today is grater than 60 days from my due date column or if my status column has been changed to "Complete"
2. Green - if the date today is between 60-30 (including) days from my due date column
2. Yellow - if the date today is between 29-15 (including) days from my due date column
3. Red - if if the date today is between 14-date today (including) OR if date on the due date column has passed.
also, can the rows on the sheet be automatically sorted based on this priority column? (all Red priority rows being top of the sheet, then yellow, green, gray).
Can some one help with this?
thank you,
Tomer
Comments
-
Paul Newcome ✭✭✭✭✭✭Here is your formula:
=IF([Due Date]@row<= TODAY(14), "Red", IF([Due Date]@row< TODAY(30), "Yellow", IF([Due Date]@row<= TODAY(60), "Green", IF(OR([Due Date]@row> TODAY(60),[email protected]= "Complete"), "Gray", ""))))
And you can sort by color. What you're looking for would be ascending order. That will put it with Red on top and Gray on bottom. It is not an automatically updating feature though. As things change/get added/get deleted, you will need to resort periodically.
Or you could pull a report from the sheet. The report does auto-sort, but is not editable like the sheet is.
The other nice thing about the sheet is that you can set it up to send a notification to whoever is in the contact column for that row whenever a status changes to "Red" (or whatever other criteria you want to send it out on).
-
Tomer ✭
thank you very much.
most of it works well, two points:
1. when applying the formula to all rows for the Priority column, the priority row gets a red - even before I have added a due date for that task. Can the cell remain blank or start with Gray until I actually add the due date for the task?
2. switching Status to Complete does not change priority status to Gray. for example, a task is 3 days before deadline, but was completed before the deadline. priority should switch to gray from red once I have marked it as complete.
thanks for the other tips!
Tomer
-
Paul Newcome ✭✭✭✭✭✭
-
Tomer ✭
this is great help Paul, much appreciated.
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/30266/\")<\/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":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+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-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
