Apex Code Development You need to sign in to do that
Don't have an account?
Date Formula Issue
Hello Experts:
I have a Date formula issue. The following date formula is not subtract a year when the date is in January:
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
)
Addtional information:
The Subtraction Month is either a 1 or 2, depending if the value is either gas or electric, Gas is 2 and Electric is 1.
And unfortunately this date formula is causing a big error in my system. It is proably something easy that I am missing.
Thank-you.
v/r
Shane Quiring
I have a Date formula issue. The following date formula is not subtract a year when the date is in January:
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
)
Addtional information:
The Subtraction Month is either a 1 or 2, depending if the value is either gas or electric, Gas is 2 and Electric is 1.
And unfortunately this date formula is causing a big error in my system. It is proably something easy that I am missing.
Thank-you.
v/r
Shane Quiring
// If Substraction Month is just one - then we want the last day of the previous month -
// ror that - just goto the first day of the Resign_Date__c month and subtract one day
NewDateField__c = IF(Subtraction_Month__c = 1,
DATE(YEAR(Resign_Date__c),MONTH(Resign_Date__c),1)-1,
// Now we know that substraction month is two, want the first day of the month before Resign_Date__c, and substract one day
IF(MONTH(Resign_Date__c)=1,
DATE(YEAR(Resign_Date__c)-1,12,1)-1,
DATE(YEAR(Resign_Date__c),MONTH(Resign_Date__c)-1,1)-1
)
)
All Answers
The YEAR ( Resign_Date__c ) + FLOOR ( (Month (Resign_Date__c ) - Subtraction_Month__c - 1)/12) statement seems like it would subtract 1 from the year (if the Resign_Date__c is January). Is that what you're wanting?
Yes exactly, if the month falls either in December or November. For example if I have date of 1/1/2016 and I subtract two months, then the date should be 11/1/2015. It works just fine if the Month is Feb, but Jan is a different story all together.
Thank-you for your assistance.
v/r
Shane Quiring
// If Substraction Month is just one - then we want the last day of the previous month -
// ror that - just goto the first day of the Resign_Date__c month and subtract one day
NewDateField__c = IF(Subtraction_Month__c = 1,
DATE(YEAR(Resign_Date__c),MONTH(Resign_Date__c),1)-1,
// Now we know that substraction month is two, want the first day of the month before Resign_Date__c, and substract one day
IF(MONTH(Resign_Date__c)=1,
DATE(YEAR(Resign_Date__c)-1,12,1)-1,
DATE(YEAR(Resign_Date__c),MONTH(Resign_Date__c)-1,1)-1
)
)
Great, that worked!
Thank-you for the assistance.
v/r
Shane Quiring