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!