+ Start a Discussion
Shane QuiringShane 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 
)

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
Best Answer chosen by Shane Quiring
JeffreyStevensJeffreyStevens
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
                                      )
                                 )
 

All Answers

JeffreyStevensJeffreyStevens
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 QuiringShane 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. 

Thank-you for your assistance.

v/r
Shane Quiring
JeffreyStevensJeffreyStevens
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 QuiringShane Quiring
Hi Jeff,

Great, that worked!

Thank-you for the assistance.

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