Smartsheet at- risk formula

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

Find more posts tagged with

Calendar View

Reports

Support

Update Requests

Accepted answers

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:

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

Try this...

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

All comments

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:

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

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.