Formula to flag red with multiple criteria

FEspo

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

 

Alejandra

Hello,

You could replace the TODAY() function with the WORKDAY() function to account for working days:

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

Phil Wightman

Hi Frank,

You could go into account settings and adjust them so only workdays are counted.  You can tick Mon-Fri and even list holidays if during the week that will not be counted.

 

FEspo

Thanks for the reply but I already had those set up and it is not working that way.

Paul Newcome

Phil,

 

This only works when using either dependencies in the project settings or when using a function that specifically refers to workdays such as the WORKDAY() function or the NETWORKDAY()/NETWORKDAYS() functions.

FEspo

Doing it this way looks like it worked. I will let it run for a while and see if nothing breaks.

Thank you!

FEspo

Dependencies are set but it still set dates that included weekend days.