Smartsheet at- risk formula

Titilope Akinlose

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

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

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:

1. End date is not blank
2. End date is in the past
3. 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

Try this...

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

Paul Newcome

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

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

Paul Newcome

Happy to help! 👍️

Titilope Akinlose

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:

1. End date is not blank
2. End date is in the past
3. 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

Try this...

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

Titilope Akinlose

You are a master! Thank you!