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

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!

Sorry for the duplication in questions.
@Paul  I tried using this technique, but get the error "too many arguments for this function"

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!

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

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!

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

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!

I can send you the sheet, I just need to take out all the input data
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!