Add a specific condition to an existing formula

Hi,

I am trying to ad a specific condition

IF(TD<[@[DUE DATE]]+7,"PIPELINE"

to an existing formula. However, the existing condition

IF(TD<[@[DUE DATE]],"CURRENT"

is interfering with the addition, and I cannot figure out where I am going wrong. I tried all sort of combinations.

The complete formula is:

=IFERROR(IF(OR([@[INVOICE AMOUNT]]="",[@[INVOICE DATE]]="",[@[DUE DATE]]="",[@[DUE DATE]]<[@[INVOICE DATE]]),"ERROR",IF([@[OUTSTANDING AMOUNT]]=0,"PAID", IF([@[OUTSTANDING AMOUNT]]>0,IF(TD<[@[DUE DATE]],"CURRENT",IF(TD=[@[DUE DATE]],"DUE TODAY",IF(TD>[@[DUE DATE]],"PAST DUE"))),IF([@[OUTSTANDING AMOUNT]]<0,"OVERPAID")))),"")

I'd appreciate your input and help in this matter.

Thank you :)

Marion

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would want to put the "CURRENT" IF before the "PIPELINE" IF.

    =IFERROR(IF(OR([@[INVOICE AMOUNT]]="",[@[INVOICE DATE]]="",[@[DUE DATE]]="",[@[DUE DATE]]<[@[INVOICE DATE]]),"ERROR",IF([@[OUTSTANDING AMOUNT]]=0,"PAID", IF([@[OUTSTANDING AMOUNT]]>0,IF(TD<[@[DUE DATE]],"CURRENT",IF(TD<[@[DUE DATE]]+7,"PIPELINE", IF(TD=[@[DUE DATE]],"DUE TODAY",IF(TD>[@[DUE DATE]],"PAST DUE")))),IF([@[OUTSTANDING AMOUNT]]<0,"OVERPAID")))),"")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Sorry for the duplication in questions.

    @Paul - I tried using this technique, but get the error "too many arguments for this function"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you explain the intended logic for this formula? How exactly do you want this to work?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • This particular column indicates the payment "Status" of an invoice.

    Right now I have everything that is TD< indicating "Current".

    What I am trying to do is label everything "Current" that's within 7 days of TD, anything due in more than 7 days I want to label "Pipeline"

    I think I am making an error in my thought process on the integration of the additional format

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I understand what you are trying to add. If you can explain the logic behind each portion, it will help me determine the correct placement of the addition.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • I'm not sure I understand what you mean by logic, sorry

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Explain exactly how the formula is supposed to work.

    If "this" is true then output "THIS".

    If "that" is less than 7 and the date is in the past then output "PAST".

    So on and so forth.


    If you can explain exactly how the entire formula is supposed to work, it may help us determine exactly where your addition needs to fit in.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • I can send you the sheet, I just need to take out all the input data

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!