IF NOT ISBLANK help

Options

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!

• ✭✭✭✭✭✭
Options

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")))))

• Options

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

• ✭✭✭✭✭✭
Options

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")))))

• ✭✭✭✭
Options

=NETWORKDAYS(Created@row, NOT(ISBLANK([Close Date]@row)))

Error message: #INVALID DATA TYPE

Thank you!

• ✭✭✭✭✭✭
Options

@Anna Makhina What exactly are you trying to accomplish?

• ✭✭✭✭
Options

@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

• ✭✭✭✭✭✭
edited 02/01/23
Options

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.

• ✭✭✭✭
Options

@Andrée Starå Thank you! It works : )

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!