Thursday, 28 November 2013

Increasing or Adding Month in Salesforce Formula Field


Problem :

Here is the scenario of my requirement,
Custom Fields:
API Name: Purchased_Date__c
Data Type: Date

API Name:  Expire_Date__c
Data Type: Formula
Return Typ: Date

For example,While creating record if the user gives date as 08/03/2013 in Purchased_Date__c field and the Corresponded formula field should return after 3 months date from the Purchased Date.


If i give Purchased Date as 09/13/2013(MM/DD/YYYY) means the Expire Date will be 12/13/2013(MM/DD/YYYY)


Solution: 
  1. DATE(
  2. year(Purchased_Date__c)
  3. + floor((month(Purchased_Date__c) + 3)/12) + if(and(month(Purchased_Date__c)=12,3>=12),-1,0)
  4. ,
  5. if( mod( month(Purchased_Date__c) + 312 ) = 012 , mod( month(Purchased_Date__c) + 312 ))
  6. ,
  7. min(
  8. day(Purchased_Date__c),
  9. case(
  10. max( mod( month(Purchased_Date__c) + 312 ) , 1),
  11. 9,30,
  12. 4,30,
  13. 6,30,
  14. 11,30,
  15. 2,if(mod((year(Purchased_Date__c)
  16. + floor((month(Purchased_Date__c) + 3)/12) + if(and(month(Purchased_Date__c)=12,3>=12),-1,0)),4)=0,29,28),
  17. 31
  18. )
  19. )
  20. )

1 comment:

  1. hi, how to write formula for joing and releving date.it means when a employee releving iwant howmany days ,months&year he worked in company

    ReplyDelete

Activities: Assign Tasks to a Queue Salesforce Lightning

Salesforce announced to assign Tasks to a Queue beginning from Spring'20 release. How does it work? In Setup, enter Queues in th...