+ Start a Discussion
Shane QuiringShane Quiring 

Formula issue

Hello Experts,

I am having an issue with a formula. Here is the concept, to subtract a certain number of months, determined by a number formula field, from a specific date. Here is the issue: the formula does subtract the month just not the year. For example if my date is 01/01/2015 and I need to subtract a month then the date should 12/01/2014. What is actually happening is the formula is subtracting the month but the year is the same, so I get (if my date is 01/01/2015 and subtract 1 month) 12/01/2015. I have searched far and wide and have not seen a resolution. I have seen resolutions for adding months and you would think that changing the "+" to " - " would reverse the action, it does, but it does not subtract the year. Here are the formulas that I have tried:

Currently using:
If ( Resign_Due__c = True,
DATE (

/*YEAR*/
YEAR ( Resign_Date__c ) + FLOOR ( (MONTH (Resign_Date__c ) - Subtraction_Month__c - 1)/12),

/*MONTH*/
CASE ( MOD ( MONTH (Resign_Date__c)- Subtraction_Month__c, 12 ),0,12,MOD ( MONTH (Resign_Date__c)- Subtraction_Month__c, 12 )),

/*DAY*/
MIN ( DAY (Resign_Date__c),
CASE ( MOD ( MONTH (Resign_Date__c)- Subtraction_Month__c,12 ) ,9,30,4,30,6,30,11,30,2,

/* return max days for February dependent on if end date is leap year */
IF ( MOD ( YEAR (Resign_Date__c) + FLOOR ( (MONTH (Resign_Date__c) - Subtraction_Month__c)/12) , 400 ) = 0 || ( MOD ( YEAR (Resign_Date__c) + FLOOR ( (MONTH (Resign_Date__c) - Subtraction_Month__c)/12) , 4 ) = 0 && MOD ( YEAR (Resign_Date__c) + FLOOR ( (MONTH (Resign_Date__c) - Subtraction_Month__c)/12) , 100 ) <> 0
)
, 29,28)

,31 ) )
),
NUll
)

Have tried also:
DATE( 
year( Start_Date__c ) 
+ floor((month(Start_Date__c) - No_of_Months__c )/12) + if(and(month(Start_Date__c)=12, No_of_Months__c >=12),-1,0), 
if( mod( month(Start_Date__c) - No_of_Months__c , 12 ) = 0, 12 , mod( month(Start_Date__c) - No_of_Months__c , 12 )), 
min(day(Start_Date__c), 
case(max( mod( month(Start_Date__c) - No_of_Months__c , 12 ) , 1), 
9,30, 
4,30, 
6,30, 
11,30, 
2,28, 
31 


)

The Second formula returns an error. Any help would be greatly appreciated.

v/r
Shane
AbhinitAbhinit
Hi Shane,
Instead of taking away the months, just try taking away the number of days. You can do something like this
Resign_Date__c - 30
It will subtract 30 days from the date.

I hope this helps.

Cheers
Abhinit
Shane QuiringShane Quiring
Hi Abhinit,

Unfortunately I need the exact date, subtracting 30 days will not work for us. The field is end of contract date field. 

Thank-you

v/r
Shane