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
Joshua Morrison 6Joshua Morrison 6 

Issue with date formula


We created a process that updates the expiration date field of an insurance policy via process builder. It works great except for polcies with an effective date in June. It is adding an extra year to the expiration date. 
So if a six month policy effective date is 06/01/2016 it is saying the expiration date is 12/01/2017 instead of 12/01/2016. 

Seems like it works perfect for all other dates and months except for June. The formula is below. If anyone could take a look at it I would grealty apprciate it.  I am sure it is just a minor error.   Thanks! 

DATE(
year([Policy__c].Effective_Date__c)
+ floor((month([Policy__c].Effective_Date__c) + 6)/12) + if(and(month([Policy__c].Effective_Date__c)=12,6>=12),-1,0)
,
if( mod( month([Policy__c].Effective_Date__c) + 6, 12 ) = 0, 12 , mod( month([Policy__c].Effective_Date__c) + 6, 12 ))
,
min(
day([Policy__c].Effective_Date__c),
case(
max( mod( month([Policy__c].Effective_Date__c) + 6, 12 ) , 1),
9,30,
4,30,
6,30,
11,30,
2,if(mod((year([Policy__c].Effective_Date__c)
+ floor((month([Policy__c].Effective_Date__c) + 6)/12) + if(and(month([Policy__c].Effective_Date__c)=12,6>=12),-1,0)),4)=0,29,28),
31
)
)
)
Best Answer chosen by Joshua Morrison 6
Rohit B ☁Rohit B ☁
Hi Joshua,
I tried with your formula and tried to solve this out. I made a little change and it is working fine for me. I've performed basic testing, please test it properly and let me know, if it doesn't work for you.
  
DATE(
year([Policy__c].Effective_Date__c)
+ if(and(month([Policy__c].Effective_Date__c)<7),0,1)
,
if( mod( month([Policy__c].Effective_Date__c) + 6, 12 ) = 0, 12 , mod( month([Policy__c].Effective_Date__c) + 6, 12 ))
,
min(
day([Policy__c].Effective_Date__c),
case(
max( mod( month([Policy__c].Effective_Date__c) + 6, 12 ) , 1),
9,30,
4,30,
6,30,
11,30,
2,if(mod((year([Policy__c].Effective_Date__c) + floor((month([Policy__c].Effective_Date__c) + 6)/12) + if(and(month([Policy__c].Effective_Date__c)=12,6>=12),-1,0)),4)=0,29,28),
31
)
)
)

If it works then Cheers :)

All Answers

Rohit B ☁Rohit B ☁
Hi Joshua,
I tried with your formula and tried to solve this out. I made a little change and it is working fine for me. I've performed basic testing, please test it properly and let me know, if it doesn't work for you.
  
DATE(
year([Policy__c].Effective_Date__c)
+ if(and(month([Policy__c].Effective_Date__c)<7),0,1)
,
if( mod( month([Policy__c].Effective_Date__c) + 6, 12 ) = 0, 12 , mod( month([Policy__c].Effective_Date__c) + 6, 12 ))
,
min(
day([Policy__c].Effective_Date__c),
case(
max( mod( month([Policy__c].Effective_Date__c) + 6, 12 ) , 1),
9,30,
4,30,
6,30,
11,30,
2,if(mod((year([Policy__c].Effective_Date__c) + floor((month([Policy__c].Effective_Date__c) + 6)/12) + if(and(month([Policy__c].Effective_Date__c)=12,6>=12),-1,0)),4)=0,29,28),
31
)
)
)

If it works then Cheers :)
This was selected as the best answer
Joshua Morrison 6Joshua Morrison 6
After doing extensive testing this seems to have solved my problem. This was such a big help to me. Thank you soooo much! 
Rohit B ☁Rohit B ☁
Hi Joshua,
If it solved your problem then please mark it as best answer, so by this way you can mark this question as solved and help others..