Formula to flag red with multiple criteria

edited 12/09/19 in Formulas and Functions

Hi. My name is Frank. I have been working on this formula for quite a while now including requests to Smartsheet support. The formula is to raise a red flag when the end date is less than 3 days away with specific status options. I finally got it working the way I want with 1 exception. It does not automatically account for workdays/weekends. I have set workdays on the sheet but it does not account for this when turning the flag red. I am including the formula and date examples. Any help would be appreciated. 

=IF(ISDATE([End Date]@row), IF(AND([End Date]@row < TODAY(2), OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "On Hold")), 1, 0))


Red Flag_1.PNG

Red Flag_2.PNG

Red Flag_3.PNG


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!