function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
asonimieSFDCasonimieSFDC 

Dynamic date in formula

I'm working on a formula and am running into issues with trying to insert a dynamic date into a field.

 

On the contact there is a picklist of Season Year, e.g. Fall 2012, Spring 2013.  I'm trying to have the formula find "Fall" and the current Year, but it doesn't appear to be working correclty.

 

Here's what I have:

 

IF(AND
           (TEXT(Contact__r.Term_of_Interest__c) = "Fall 2012",
           (TEXT(Contact__r.Status__c) = "Admit" || TEXT(Contact__r.Status__c) = "Confirmed")),
            "Commitment",
IF(AND
        (TEXT(Contact__r.Term_of_Interest__c) = "Fall" & TEXT(YEAR(TODAY())),
         TODAY()<DATE(YEAR(TODAY()-365), 09, 01)), "ChoiceSet",
   IF (AND
        (TEXT(Contact__r.Term_of_Interest__c) = "Fall" & TEXT(YEAR(TODAY())),
          TODAY()>DATE(YEAR(TODAY()-365), 09, 01)), "Application",
     ""
       )
   )
)

 

In my head it should be joining Fall & Current Year, so Fall 2012, and next year it would be Fall 2013.  What am I missing?

Best Answer chosen by Admin (Salesforce Developers) 
asonimieSFDCasonimieSFDC

Ultimately I ended up going with:

 

IF( MONTH(TODAY()) < 9 && YEAR(TODAY()) < VALUE(RIGHT(Term_of_Interest__c,4)) , 
"Project Choice Set" , 
"Project Application" 
) 

All Answers

Navatar_DbSupNavatar_DbSup

Hi,

 

If you are checking Picklist value then you have to use “ISPICKVAL”

 

ISPICKVAL(picklist_field, text_literal)

Calculates the number of days since the contract was activated. If the contract status is not “Activated,” this field is blank.

IF(ISPICKVAL(Status, "Activated"), NOW()-ActivatedDate, null)

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved. 

asonimieSFDCasonimieSFDC

I was missing a space.  I got the formula to work using:

 

IF(AND 
(TEXT(Contact__r.Term_of_Interest__c) = "Fall " & TEXT(YEAR(TODAY())), 
TODAY()<DATE(YEAR(TODAY()-365), 09, 01)), 
"ChoiceSet", 
IF (AND 
(TEXT(Contact__r.Term_of_Interest__c) = "Fall " & TEXT(YEAR(TODAY())), 
TODAY()>DATE(YEAR(TODAY()-365), 09, 01)), 
"Application", 
IF(TEXT(Contact__r.Status__c) = "Admit" || TEXT(Contact__r.Status__c) = "Confirmed", 
"Commitment", "" 


)

 

But then I realized it wouldn't meet all my needs.

 

I need the formula to be able to Calculate something like:

 

If Term of interest = Fall X (where X is a year)

and Today's date > 9/1/(X-1) Set field to Application

 

but

 

If Term of interest = Fall X (where X is a year)

and Today's date < 9/1/(X-1) Set field to Choice



asonimieSFDCasonimieSFDC

Ultimately I ended up going with:

 

IF( MONTH(TODAY()) < 9 && YEAR(TODAY()) < VALUE(RIGHT(Term_of_Interest__c,4)) , 
"Project Choice Set" , 
"Project Application" 
) 

This was selected as the best answer