ShowAll Questionssorted byDate Posted
Shane Quiring

# 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
)

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
Best Answer chosen by Shane Quiring
JeffreyStevens
Maybe something like this?....

// 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
)
)

JeffreyStevens
What are you trying to calculate?

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?
Shane Quiring
Hi Jeffrey,

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.

v/r
Shane Quiring
JeffreyStevens
Maybe something like this?....

// 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
)
)

This was selected as the best answer
Shane Quiring
Hi Jeff,

Great, that worked!

Thank-you for the assistance.

v/r
Shane Quiring
JeffreyStevens
Great! - Be sure to mark the correct post as "Solved" also