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

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.

Answers

  • Sing C
    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

    Sing Chen

    Process Architect, Ceridian

    LinkedIn

  • jmyzk_cloudsmart_jp
    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 ")