Formula to flag red with multiple criteria

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([email protected] = "Not Started", [email protected] = "In Progress", [email protected] = "On Hold")), 1, 0))

 

Red Flag_1.PNG

Red Flag_2.PNG

Red Flag_3.PNG

Comments

  • Alejandra
    Alejandra Employee

    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([email protected] = "Not Started", [email protected] = "In Progress", [email protected] = "On Hold")), 1, 0))

  • Phil Wightman
    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.

     

    Account settings.JPG

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

  • Paul Newcome
    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.

    thinkspi.com

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

    Thank you!

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