If Formula with Dropdown conditions based on Dates
I'm looking for a formula that could be integrated into a drop down field based on a past date.
I'm trying to have the "Status" column automatically flip toOff-Trackif the date in the "Finish" column is now in the past and the "Status" column is not already inAt RiskorCompletedstatus.
I'm able to use =IF(Finish1 < TODAY(), "Off Track") but this is not taking in to account if the current "Status" column is not already in At Risk or Completed status. Does anyone know if there is a formula available for this?
Best Answer
-
Purnima Gore ✭✭✭✭✭✭Hi
Try something like this
=IF([email protected]= "Complete", "Complete", IF(AND([email protected]< TODAY(), (OR([email protected]= "In Progress",[email protected]= "Not Started"))), "Late", IF(AND([email protected]> TODAY(),[email protected]< (TODAY() + 14), (OR([email protected]= "In Progress",[email protected]= "Not Started"))), "Next 14 Days", "Future")))
I use this because card view is only available where you use dropdown and so this gives me this in a card view if I need it.
Hope that helps.
Kind regards
Purnima
Purnima Gore
Cierr Limited
Your Time is Important, you want to Stay on Track, We can help you use the Right Tools
Answers
-
Ramzi K ✭✭✭✭✭Use and AND clause to combine your conditions:
=IF(AND(Finish1 < TODAY(), Status1 <> "At Risk", Status1 <> "Completed"), "Off Track")
I hope this helps,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me:[email protected]
If this post helped you out, please help the Community bymarking it as the accepted answer/helpful.
-
Thank you for that!
If I could steal some more of your knowledge, one thing i'm struggling with is inputting the formula to the cell which has a drop down. Each row will start off as Not Started, and as the workflow goes on the status will manually change.
How can I pre-set the cell to have the formula and still stay active as the drop down items change.
-
Purnima Gore ✭✭✭✭✭✭
Hi
Try something like this
=IF([email protected]= "Complete", "Complete", IF(AND([email protected]< TODAY(), (OR([email protected]= "In Progress",[email protected]= "Not Started"))), "Late", IF(AND([email protected]> TODAY(),[email protected]< (TODAY() + 14), (OR([email protected]= "In Progress",[email protected]= "Not Started"))), "Next 14 Days", "Future")))
I use this because card view is only available where you use dropdown and so this gives me this in a card view if I need it.
Hope that helps.
Kind regards
Purnima
Purnima Gore
Cierr Limited
Your Time is Important, you want to Stay on Track, We can help you use the Right Tools
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/72220/\")<\/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":33,"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":"
