Nested IFs to populate Priority based on set number of days prior to start date
I'm trying to populate a Priority column with Urgent (within 1 week from today), High (1 - 3 weeks from today), Med (3 - 4 weeks from today), Low (4+ weeks from today), Unknown (no date provided) based on the # of weeks out for the start date of the effort.
Both of these work for just 1 Date Range:
=IF({Sheet Range 9} <= TODAY(7), "Urgent", "TBD") -> Reference to separate sheet
=IF(NETDAYS([Column19]@row, TODAY()) <= 7, "Urgent", "TBD") -> Reference within existing sheet
Here's what's not working to get this to populate Urgent, High, Med for multiple Date Ranges. I didn't try to add Low or Unknown since I'm already struggling with the nested IFs.
=IF({Sheet Range 9} <= TODAY(7), “Urgent”, IF(AND({Sheet Range 9} > TODAY(+7), {Sheet Range 9} <= TODAY(+21)), “High”, “Med”))
Best Answer
-
You have directional quotes on the formula that doesn't work. Everything else looks correct about it
The typical causes of this are either developing the formula in word, or i've heard of foreign language keyboards or keyboard settings causing this. I recommend if you want to use a different application to build formulas either use a code editor, or use the stock version of notepad on your machine.
Answers
-
You have directional quotes on the formula that doesn't work. Everything else looks correct about it
The typical causes of this are either developing the formula in word, or i've heard of foreign language keyboards or keyboard settings causing this. I recommend if you want to use a different application to build formulas either use a code editor, or use the stock version of notepad on your machine.
-
Whoa, who would have thought that would matter! That fixed the issue, now I just have to figure out the rest of the nested IFs.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!