And Formula - Can't get it to work. Urgent

Victoria Vanden Berg

An idea is simple. There is a column "Finish" defined as date column. and % Complete column.

What i need to do is to highlight the risk if Finish is past due or lesser then Today and % complete is below 100.

Here is the formula: =IF(AND([Finish] < TODAY(), [% Complete] < 100%)), "At Risk", "--""))

I tried % complete being 1, 100% and 100 with the same result

The link to the doc: (8) EBD1 SAP ECC Stream - Smartsheet.com. Please help. I need it urgently. Thank you.

Find more posts tagged with

Accepted answers

All comments

Sing C

Hi @Victoria Vanden Berg,

A couple of things I've spotted in your formula:

It appears your column references are missing the row reference, example:

Finish@row - instead of Finish

The two sets of double closing brackets only need to be single brackets.
The pair of double quotes at the end aren't needed - just one double quote.
The % valuation should be <1 (which I know you mentioned you had tried but the other issues wouldn't have made this one easy to spot).

I've tried this on a sample sheet and it works for me:

=IF(AND(Finish@row < TODAY(), [% Complete]@row < 1), "At Risk", "--")

Give that a try and let me know how you get on. Hope that works.

Thanks,

Sing

jmyzk_cloudsmart_jp

Hi @Victoria Vanden Berg

Try this:😀

=IF(AND(Finish@row < TODAY(), [% Complete]@row < 1), "At Risk", " --- ")

FYI, changing your formula, as shown in bold, should fix the errors.

=IF(AND([Finish]@row < TODAY(), [% Complete]@row < 1)), "At Risk", "--""))

(add @row and remove unnecessary ) and ")

https://app.smartsheet.com/b/publish?EQBCT=700b614d49d24e4c83dc9f630c5ff69f

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.