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:
- DATE(
- year(Purchased_Date__c)
- + floor((month(Purchased_Date__c) + 3)/12) + if(and(month(Purchased_Date__c)=12,3>=12),-1,0)
- ,
- if( mod( month(Purchased_Date__c) + 3, 12 ) = 0, 12 , mod( month(Purchased_Date__c) + 3, 12 ))
- ,
- min(
- day(Purchased_Date__c),
- case(
- max( mod( month(Purchased_Date__c) + 3, 12 ) , 1),
- 9,30,
- 4,30,
- 6,30,
- 11,30,
- 2,if(mod((year(Purchased_Date__c)
- + floor((month(Purchased_Date__c) + 3)/12) + if(and(month(Purchased_Date__c)=12,3>=12),-1,0)),4)=0,29,28),
- 31
- )
- )
- )
Hello, thanks for the formula. I think it's great. I'm very new to salesforce so haven't had a chance to dive into the code. I'm using your formula to calculate 6 months and 18 months after a specific date. During testing I found that if my initial date was 6/30/2013 and I used the formula above to calculate 6 months into the future, the result I received was 12/30/2013. Unfortunately, I wanted to see 12/31/2013. Have you run into this case and if so, have you updated this formula to take this type of scenario into account. Your assistance is appreciated.
ReplyDeletehi, 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