Smartsheet at- risk formula

Titilope Akinlose · 06/22/20

Hi all, I'm having a little trouble with creating the right formula for my "at risk" / "attn" column.

I only want the column to flag is the status is not complete and the target end date is past due. My current formula is =IF(AND(Status301 <> "Complete", [Target End Date]301 <= TODAY()), 1, 0)

Currently, the column flags even when the target end date is blank. I only want the flag when the status is not complete, and the target end date is past due, AND the target end date is not blank.

Please help!

Thanks

Paul Newcome · 06/23/20

Try somethingn like this...

=IF([Target End Date]301 <> "", IF(AND(Status301 <> "Complete", [Target End Date]301 <= TODAY()), 1, 0))

This will take your original formula and run it only if the [Target End Date] is not blank.

Titilope Akinlose · 02/02/21

Hi Paul,

I have an updated formula I would like to use, and I ma having trouble and not sure whether to use the AND/OR feature. I only want the flag when the:

End date is not blank
End date is in the past
And status is not complete OR received.

Current formula is you helped me with is

=IF([End Date]1 <> "", IF(AND(Status1 <> "Completed", [End Date]1 <= TODAY()), 1, 0))

Not sure how to incorporate the status is not complete OR received component.

Any help will be appreciated.

Thank you!

Paul Newcome · 02/02/21

Try this...

=IF([End Date]1 <> "", IF(AND(Status1 <> "Completed", Status1 <> "Received", [End Date]1 <= TODAY()), 1, 0))

Paul Newcome · 06/23/20

Try somethingn like this...

=IF([Target End Date]301 <> "", IF(AND(Status301 <> "Complete", [Target End Date]301 <= TODAY()), 1, 0))

This will take your original formula and run it only if the [Target End Date] is not blank.

Titilope Akinlose · 06/23/20

Thank you so much, it worked perfectly!!!!!!!!

Paul Newcome · 06/23/20

Happy to help! 👍️

Titilope Akinlose · 02/02/21

Hi Paul,

I have an updated formula I would like to use, and I ma having trouble and not sure whether to use the AND/OR feature. I only want the flag when the:

End date is not blank
End date is in the past
And status is not complete OR received.

Current formula is you helped me with is

=IF([End Date]1 <> "", IF(AND(Status1 <> "Completed", [End Date]1 <= TODAY()), 1, 0))

Not sure how to incorporate the status is not complete OR received component.

Any help will be appreciated.

Thank you!

Paul Newcome · 02/02/21

Try this...

=IF([End Date]1 <> "", IF(AND(Status1 <> "Completed", Status1 <> "Received", [End Date]1 <= TODAY()), 1, 0))

Titilope Akinlose · 02/03/21

You are a master! Thank you!

Smartsheet at- risk formula

Best Answers

Answers

Help Article Resources

Categories