Options
Justyna Wlodarz ✭
04/15/20 in Formulas and Functions
Hi Smartsheet Heroes :)
I need o create function using this conditions and symbols (Green, Amber, Red) based on numeric column with Delay...
Condition 1: IF Delay less then 1 week then GREEN
Condition 2: IF Delay between more then 1 week and less then 4 weeks then AMBER
Condition 3: IF Delay more then 4 weeks then RED.
Do you know how the syntax should be created? I tried to create in million times and still doesn't work :(
Cheers,
Justyna
0 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
04/16/20 Answer ✓
Options
Hi Justyna,
If I understand you correctly something like this should work.
=IF(OR(ISBLANK(Start@row); ISBLANK(End@row)); ""; IF(End@row - Start@row > 28; "Red"; IF(End@row - Start@row < 7; "Green"; IF(End@row - Start@row < 28; "Yellow"))))
The same version but with the below changes for convenience.
=IF(OR(ISBLANK(Start@row), ISBLANK(End@row)), "", IF(End@row - Start@row > 28, "Red", IF(End@row - Start@row < 7, "Green", IF(End@row - Start@row < 28, "Yellow"))))
Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.
Did that work?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
5 · Share on FacebookShare on Twitter
Answers
Andrée Starå ✭✭✭✭✭✭
04/16/20 Answer ✓
Options
Hi Justyna,
If I understand you correctly something like this should work.
=IF(OR(ISBLANK(Start@row); ISBLANK(End@row)); ""; IF(End@row - Start@row > 28; "Red"; IF(End@row - Start@row < 7; "Green"; IF(End@row - Start@row < 28; "Yellow"))))
The same version but with the below changes for convenience.
=IF(OR(ISBLANK(Start@row), ISBLANK(End@row)), "", IF(End@row - Start@row > 28, "Red", IF(End@row - Start@row < 7, "Green", IF(End@row - Start@row < 28, "Yellow"))))
Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.
Did that work?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
5 · Share on FacebookShare on Twitter
Justyna Wlodarz ✭
04/19/20
Options
It works! :)
Thank you so much Andree :)
0 · Share on FacebookShare on Twitter
Andrée Starå ✭✭✭✭✭✭
04/27/20
Options
Excellent!
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
0 · Share on FacebookShare on Twitter
Help Article Resources
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- 59.4K Get Help
- 90 Global Discussions
- 94 Industry Talk
- 400 Announcements
- 12 Community Corner Newsletter
- 71 Brandfolder
- 121 Just for fun
- 46 Community Job Board
- 22 Member Spotlight
- 1 SmartStories
- 251 Events
- 13 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!