IF NOT ISBLANK help
I'm trying to create a formula that will display the different colored dots in the status bar. I had success at first but one part of the formula isn't working with the others. I need it to display BLANK if Patient is blank, Gray if patient is filled in but Order Request Date is blank, Green if Complete date is filled in, Red if Complete is blank and return visit is within 1 week, and Yellow if Complete date is blank and return visit is within 2 weeks.
I was able to get this formula to work but when I try to input the first part so that no color shows up the whole thing fails :
=IF(ISBLANK([Order/Request Date]@row), "Gray", IF(AND(ISDATE([Current Visit Date]@row), ISDATE([Completed Date]@row)), "Green", IF(AND([Next Visit Date]@row - 7 <= TODAY(), (ISBLANK([Completed Date]@row))), "Red", IF(AND([Next Visit Date]@row - 6 >= TODAY(), (ISBLANK([Completed Date]@row))), "Yellow"))))
Also here is a screenshot of the sheet
Hope someone can help, thanks so much smartsheet community!
Answers
-
Give this one a whirl...
=IF(ISBLANK(Patient@row), "", IF(ISBLANK([Order/Request Date]@row), "Gray", IF(AND(ISDATE([Current Visit Date]@row), ISDATE([Completed Date]@row)), "Green", IF(AND([Next Visit Date]@row - 7 <= TODAY(), (ISBLANK([Completed Date]@row))), "Red", IF(AND([Next Visit Date]@row - 6 >= TODAY(), (ISBLANK([Completed Date]@row))), "Yellow")))))
-
Hi Paul,
Your formula works, but dates greater than 2 weeks also appear as yellow. Is there a way to have dates greater than 2 weeks from today still appear default "Gray", within 2 weeks "Yellow", and within 1 week "Red". I've tried many different formulas but smartsheet can't seem to recognize the limitations and gives me back tons of errors.
Thanks,
-Z
-
My apologies. I just took your original formula and took care of the ISBLANK portion. Try this one:
=IF(ISBLANK(Patient@row), "", IF(ISBLANK([Order Request Date]@row), "Gray", IF(ISDATE([Completed Date]@row), "Green", IF([Next Visit Date]@row <= TODAY(7), "Red", IF([Next Visit Date]@row <= TODAY(14), "Yellow")))))
-
Hello! Could you please help to understand what is wrong with formula?
=NETWORKDAYS(Created@row, NOT(ISBLANK([Close Date]@row)))
Error message: #INVALID DATA TYPE
Thank you!
-
@Anna Makhina What exactly are you trying to accomplish?
-
@Paul Newcome Hi Paul, I would like to calculate how many working days it takes to complete requests.
This formula works fine for me =NETWORKDAYS(Created@row, [Close Date]@row)
However, for open requests the end date (Close Date) is missing. I would like to skip these cells. I thought NOT(ISBLANK could be a suitable solution.
I cannot understand what is wrong with new formula =NETWORKDAYS(Created@row, NOT(ISBLANK([Close Date]@row)))
Error message: #INVALID DATA TYPE
Thank you for your response!
-
I hope you're well and safe!
Try something like this.
=IFERROR(NETWORKDAYS(Created@row, [Close Date]@row), "")
Did that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. 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.
-
@Andrée Starå Thank you! It works : )
-
Excellent!
You're more than welcome!
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. 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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!