You need to sign in to do that
Don't have an account?
Shane 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
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
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
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