Automate At Risk flag
I am very new to Smartsheet - trying to figure this out and could use some help.
I need to have my At Risk flag marked automatically if a task is due [Complete By] within three days and the [% Complete] is not 100%.
The one I am currently using is not working: =IF(AND([Complete By]@row >= TODAY(2), [% Complete]@row < 1), 1, 0)
I know this has been asked before - I have been reading them all (that's where I got that formula) and can't seem to find one that will work.
Best Answer
-
You are actually very very close. One thing I do is think of dates as numbers where TODAY() is zero. So tomorrow would be TODAY(1) and yesterday would be TODAY(-1).
Using that logic, we can update your formula with a very minor tweak like so (bold):
=IF(AND([Complete By]@row >= TODAY(-3), [% Complete]@row < 1), 1, 0)
Answers
-
You are actually very very close. One thing I do is think of dates as numbers where TODAY() is zero. So tomorrow would be TODAY(1) and yesterday would be TODAY(-1).
Using that logic, we can update your formula with a very minor tweak like so (bold):
=IF(AND([Complete By]@row >= TODAY(-3), [% Complete]@row < 1), 1, 0)
-
Welp....I feel dumb.
Thanks for the clarification - that worked like a charm and now I understand it better.
-
No need to feel "dumb". You gotta learn somehow, and I am happy to help. 👍️
-
Where do you put this formula? Into each cell that contains a flag? I'm trying to do the same thing. Help!
-
@Tina D Yes. It would go in the flag column.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.7K Get Help
- 63 Global Discussions
- 68 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!