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(Status@row = "Not Started", Status@row = "In Progress", Status@row = "On Hold")), 1, 0))
Comments
-
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.
-
Thanks for the reply but I already had those set up and it is not working that way.
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!