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 to Off-Track if the date in the "Finish" column is now in the past and the "Status" column is not already in At Risk or Completed status.
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
-
Hi
Try something like this
=IF(Status@row = "Complete", "Complete", IF(AND(Finish@row < TODAY(), (OR(Status@row = "In Progress", Status@row = "Not Started"))), "Late", IF(AND(Finish@row > TODAY(), Finish@row < (TODAY() + 14), (OR(Status@row = "In Progress", Status@row = "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
-
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: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking 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.
-
Hi
Try something like this
=IF(Status@row = "Complete", "Complete", IF(AND(Finish@row < TODAY(), (OR(Status@row = "In Progress", Status@row = "Not Started"))), "Late", IF(AND(Finish@row > TODAY(), Finish@row < (TODAY() + 14), (OR(Status@row = "In Progress", Status@row = "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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!