Automate At Risk flag

Cara Sikes

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.

Paul Newcome

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)

Paul Newcome

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)

Cara Sikes

Welp....I feel dumb.

Thanks for the clarification - that worked like a charm and now I understand it better.

Paul Newcome

No need to feel "dumb". You gotta learn somehow, and I am happy to help. 👍️

Tina D

Where do you put this formula? Into each cell that contains a flag? I'm trying to do the same thing. Help!

Paul Newcome

@Tina D Yes. It would go in the flag column.