You need to sign in to do that
Don't have an account?
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?
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
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.
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
Ultimately I ended up going with:
IF( MONTH(TODAY()) < 9 && YEAR(TODAY()) < VALUE(RIGHT(Term_of_Interest__c,4)) ,
"Project Choice Set" ,
"Project Application"
)