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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!