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!

Find more posts tagged with

Sheets

Formulas

Support

functionality

IT & Operations

Accepted answers

All comments

Paul Newcome

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

Zachary Mitchell

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

Paul Newcome

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

Anna Makhina

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!

Paul Newcome

@Anna Makhina What exactly are you trying to accomplish?

Anna Makhina

@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!

Andrée Starå

Hi @Anna Makhina

I hope you're well and safe!

Try something like this.

=IFERROR(NETWORKDAYS(Created@row, [Close Date]@row), "")

https://help.smartsheet.com/function/iferror

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!

Anna Makhina

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

Andrée Starå

@Anna Makhina

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.