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.

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