Add a specific condition to an existing formula

Marion1972

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

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")))),"")

Marion1972

Sorry for the duplication in questions.

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

Paul Newcome

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

Marion1972

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

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.

Marion1972

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

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.

Marion1972

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