function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
vleandrovleandro 

Question on IF Statement within a Formula Field

I have a formula that pretty much works correct:

IF(ISNULL(dueDateTime__c), "YES",
( IF( ISNULL(closeDateTime__c), ( IF(dueDateTime__c >=  NOW(), "YES", "NO")),
( IF(dueDateTime__c >= closeDateTime__c, "YES", "NO"))
))
)
So far so good...however I have yet another condition I want to check.  

Effecively if the Completed Date is not null and Close Date is greater than or equal to Due Date, then the field is "YES".

So I came up with this:

IF(ISNULL(dueDateTime__c), "YES", 
	( IF( ISNULL(closeDateTime__c), ( IF(dueDateTime__c >= 	NOW(), "YES", "NO")), 
	( IF(dueDateTime__c >= closeDateTime__c, "YES", "NO")),
	( IF (AND( NOT(ISNULL(CompletedDate__c), (IF(closeDateTime__c >= dueDateTime__c, "YES", "NO")))))))))

However, I get the following error:

Error: Syntax error. Missing ')'

I'll spare you the details of all the gyrations I've gone through the the ')'.  Suffice it to say the cursor goes to the comma ( , ) after the (CompletedDate__c) field.

As always your help is always appreciated!  Thank you!

Best Answer chosen by vleandro
ForceMantis (Amit Jain)ForceMantis (Amit Jain)
Try this one:

IF(ISNULL(dueDateTime__c), "YES",
( IF( ISNULL(closeDateTime__c),  IF(dueDateTime__c >=  NOW(), "YES", "NO"),
( IF (AND( NOT(ISNULL(CompletedDate__c)), (closeDateTime__c >= dueDateTime__c)), "YES", IF(dueDateTime__c >= closeDateTime__c, "YES", "NO")))
)))

All Answers

ForceMantis (Amit Jain)ForceMantis (Amit Jain)
If I have understand your logic correctly try this:

IF(ISNULL(dueDateTime__c), "YES",
( IF( ISNULL(closeDateTime__c), ( IF(dueDateTime__c >=  NOW(), "YES", "NO")),
( IF (AND( NOT(ISNULL(CompletedDate__c)), (closeDateTime__c >= dueDateTime__c))), "YES", ( IF(dueDateTime__c >= closeDateTime__c, "YES", "NO")))))
)
vleandrovleandro
Still get:
Error: Syntax error. Missing ')'

Except now we're here:

(BMCServiceDesk__closeDateTime__c >= BMCServiceDesk__dueDateTime__c))), "YES"
....the cursor moves to the comma before "YES"
ForceMantis (Amit Jain)ForceMantis (Amit Jain)
Try this one:

IF(ISNULL(dueDateTime__c), "YES",
( IF( ISNULL(closeDateTime__c),  IF(dueDateTime__c >=  NOW(), "YES", "NO"),
( IF (AND( NOT(ISNULL(CompletedDate__c)), (closeDateTime__c >= dueDateTime__c)), "YES", IF(dueDateTime__c >= closeDateTime__c, "YES", "NO")))
)))
This was selected as the best answer
vleandrovleandro
Worked like a champ!  (At least once I got my workflow in place to actually populate the Completed Date field!)  Thank you so much for your assistance!
vleandrovleandro
btw...in case anyone else comes along on this thread; I accidently marked the wrong answer as best answer....it's the second formula that Amit posted:

IF(ISNULL(dueDateTime__c), "YES",
( IF( ISNULL(closeDateTime__c),  IF(dueDateTime__c >=  NOW(), "YES", "NO"),
( IF (AND( NOT(ISNULL(CompletedDate__c)), (closeDateTime__c >= dueDateTime__c)), "YES", IF(dueDateTime__c >= closeDateTime__c, "YES", "NO")))
)))

I tried to change it...but the discussion board system isn't being very cooperative...
ForceMantis (Amit Jain)ForceMantis (Amit Jain)
I am glad to hear that it helped you.