Starting November 20, the site will be set to read-only.
On December 4, 2023,
Apex Code Development (90768)
General Development (55146)
Visualforce Development (37251)
Lightning (18265)
APIs and Integration (17146)
Trailhead (11680)
Formulas & Validation Rules Discussion (11337)
Other Salesforce Applications (8116)
Jobs Board (6655)
Force.com Sites & Site.com (4842)
Mobile (2694)
You need to sign in to do that
Don't have an account?
LeeC Hello, Could someone please help me with this formula:
IF(
AND ( Stage_Duration__c > 1,
IF(ISPICKVAL( Job_Status__c , "Work to be Scheduled"), "-= OVER DUE =-"
I have two fields "Stage Duration" and "Job Status" I want the "Over due" message to appear when Stage duration is >1 and Job_Status = Work to be Scheduled.
Thanks in advance.
Try this...
IF (AND(Stage_Duration__c > 5,TEXT(Job_Status__c)="Stage 1") ,"Overdue",
IF (AND(Stage_Duration__c > 10,TEXT(Job_Status__c)="Stage 2") ,"Overdue",
IF (AND(Stage_Duration__c > 15,TEXT(Job_Status__c)="Stage 3") ,"Overdue",
IF (AND(Stage_Duration__c > 20,TEXT(Job_Status__c)="Stage 4") ,"Overdue",
IF (AND(Stage_Duration__c > 25,TEXT(Job_Status__c)="Stage 5") ,"Overdue",
IF (AND(Stage_Duration__c > 30,TEXT(Job_Status__c)="Stage 6") ,"Overdue",
'')))))
)
All Answers
I don't think you need that second nested if:
IF(AND(Stage_Duration__c > 1,ISPICKVAL(Job_Status__c,"Work to be Scheduled")),"-=OVER DUE=-","")
That's assuming that you don't want a value to appear if the criteria aren't meant. If you do, you could put something in that last set of quotations that said, "ON TIME" or whatever other message you found appropriate.
Hello, Thanks Wyam that works great. Would you have any idea how I could expand the formula to include a couple of different options?
Stage Duration could be: Work to be Scheduled + Stage Duration >1, Work Scheduled + Stage Duration >20, Working + Stage Duration >30, Temp + Stage Duration >40, Complete + Stage Duration >50.
When the two values are true the message "-=OVER DUE=-" appears.
Thanks.
You could simply used nested "If" statements for that.
If(AND(pickvalue="X",Stage Duration > "A"),True condition,if(AND(pickval="Y",Stage Duration > "B"), True Condition,if(AND(etc...
Hi Wyam, thanks for the fast reply. I have tried to use the formula but i'm getting a little stuck. Could you please take a look?
IF(AND(ISPICKVAL( Job_Status__c , "Stage 1")), Stage_Duration__c > 5,
"STAGE 1",
If(AND( ISPICKVAL( Job_Status__c , "Stage 2"),Stage_Duration__c> 10,
"STAGE 2",
If(AND( ISPICKVAL( Job_Status__c , "Stage 3") ,Stage_Duration__c> 15,
"STAGE 3",
If(AND (ISPICKVAL( Job_Status__c , "Stage 4") ,Stage_Duration__c > 20,
"STAGE 4",
If(AND( ISPICKVAL( Job_Status__c , "Stage 5") , Stage_Duration__c > 25,
"STAGE 5",
If(AND( ISPICKVAL( Job_Status__c , "Stage 6") , Stage_Duration__c > 30,
"STAGE 6"))))
Could you paste your previous post using the code paste clipboard? It's the icon that is two items to the left of the smiley face on the reply screen. It's a clipboard with a "C" on it.
The smiley faces don't translate well even on a text pad.
Sure - Thanks
IF(AND(ISPICKVAL( Job_Status__c , "Stage 1"), Stage_Duration__c > 5, "STAGE 1", If(AND( ISPICKVAL( Job_Status__c , "Stage 2",Stage_Duration__c> 10, "STAGE 2", If(AND( ISPICKVAL( Job_Status__c , "Stage 3" ,Stage_Duration__c> 15, "STAGE 3", If(AND (ISPICKVAL( Job_Status__c , "Stage 4" ,Stage_Duration__c > 20, "STAGE 4", If(AND( ISPICKVAL( Job_Status__c , "Stage 5" , Stage_Duration__c > 25, "STAGE 5", If(AND( ISPICKVAL( Job_Status__c , "Stage 6" , Stage_Duration__c > 30, "STAGE 6")))
So if I read your logic properly, you're basically trying to call an "Overdue" at each stage right?
If the Job Status is Stage 1 but under 5 duration, show Stage 1 else show "Overdue"
If the Job Status is Stage 2 but under 10 duration, show Stage 2 else show "Overdue"
What happens if you mix the values? Say if you have a job status of Stage 1 but a duration of 30? Is it still overdue?
Also, your formula doesn't have a false statement. Basically it outlines all the possibilities but doesn't let the calculation know what to do if the result is FALSE.
Yup - The "Overdue" value should only display if the statement is true. If you mix the values then it shouldn't display. If the value is FALSE I dont want anything to display, so I guess its just "".
If that's the case then it gets complicated/cumbersome.
You now not only have to tell the system when to add something to the result field but you have to define what that is based on a series of true/false items.
If you had the logic to simply write "Overdue" when the field is over a series of durations, that would be straight forward. But to write various of Stage or Blank based on the results of the two criteria, it would probably take a series of workflows and field updates.
Sorry my advice didn't pan out.
Good luck.
Try this...
IF (AND(Stage_Duration__c > 5,TEXT(Job_Status__c)="Stage 1") ,"Overdue",
IF (AND(Stage_Duration__c > 10,TEXT(Job_Status__c)="Stage 2") ,"Overdue",
IF (AND(Stage_Duration__c > 15,TEXT(Job_Status__c)="Stage 3") ,"Overdue",
IF (AND(Stage_Duration__c > 20,TEXT(Job_Status__c)="Stage 4") ,"Overdue",
IF (AND(Stage_Duration__c > 25,TEXT(Job_Status__c)="Stage 5") ,"Overdue",
IF (AND(Stage_Duration__c > 30,TEXT(Job_Status__c)="Stage 6") ,"Overdue",
'')))))
)
Thank you! that works great.
Waym, thats for all your help as well.