Apex Code Development You need to sign in to do that
Don't have an account?
waylonatcim Adding months to dates
I'm wondering if there is an easier way to accomplish what I'm trying to do. I have a date field, Date_signed__c, and a number field, Duration_in_months_c. I am calculating a new date field that is basically Date_signed + Duration_in_months using the following formula:
DATE( YEAR( Date_Signed__c ) + ROUND(Duration_in_months__c / 12 ,0), MOD(MONTH( Date_Signed__c) + Duration_in_months__c,12) , DAY( Date_Signed__c) )
I noticed that if you just add a number to a date field it adds days, but I'm not sure of an easy way to add months. Is there a simpler way that I am missing?
Thanks
The above formula didn't actually work for all cases so I had to add some more logic:
DATE( YEAR( Date_Signed__c ) + FLOOR((MONTH( Date_Signed__c)+Duration_in_months__c) / 12 ), IF(MOD(MONTH( Date_Signed__c) + Duration_in_months__c,12) = 0, 1, MOD(MONTH( Date_Signed__c) + Duration_in_months__c,12)) , DAY( Date_Signed__c) )
Thanks for the reply.
You are right, I noticed this during testing so I started adding more logic using a case statement but ran out of the available characters I could use in the formula and now I'm stuck.
DATE ( FLOOR(((YEAR( Expiration_Date__c ) * 12) + MONTH ( Expiration_Date__c ) - Service_Months__c) / 12) - IF(AND( DAY( Expiration_Date__c ) < 30,MOD(MONTH ( Expiration_Date__c ) - Service_Months__c,12) = 0), 1, 0), IF(MOD(MONTH ( Expiration_Date__c ) - Service_Months__c + IF(DAY(Expiration_Date__c) >= 30, 1,0),12) = 0, 12, MOD((YEAR( Expiration_Date__c ) * 12) + MONTH ( Expiration_Date__c ) - Service_Months__c + IF(DAY(Expiration_Date__c) >= 30, 1,0),12)), 1 )
DATE ( FLOOR(((YEAR( Start_Date__c ) * 12) + MONTH ( Start_Date__c ) + Term_In_Months__c) / 12) - IF(MOD(MONTH ( Start_Date__c ) + Term_In_Months__c,12) = 0, 1, 0), IF(MOD(MONTH ( Start_Date__c ) + Term_In_Months__c,12) = 0, 12, MOD((YEAR( Start_Date__c ) * 12) + MONTH ( Start_Date__c ) + Term_In_Months__c,12)), 1 )
And if you don't need the field for reporting, here is the html s-control. Anyone know visualforce well enought to convert?
<table style="background-color: #F3F3EC;"> <tr> <td width="18%" style="color: #333333; text-align: right; font-size: 71%; font-family: 'Arial','Helvetica',sans-serif; font-weight: bold; padding: 0px 9px 0px 2px; vertical-align: top;"> Next Renewal Date </td> <td width="32%" style="text-align: left; font-size: 75%; font-family: 'Arial','Helvetica',sans-serif; padding: 0px 2px 0px 10px; vertical-align: top;"> {!DATE(IF((Opportunity.Terms_Completed__c - FLOOR(Opportunity.Terms_Completed__c)) = 0, YEAR(TODAY()),YEAR(Opportunity.CloseDate) + FLOOR((((FLOOR((((YEAR( TODAY() ) * 12) + MONTH( TODAY() )) - ((YEAR(Opportunity.CloseDate) * 12) + MONTH(Opportunity.CloseDate))) / Opportunity.Contract_Length__c) + 1) * Opportunity.Contract_Length__c) + (MONTH(Opportunity.CloseDate) - 1)) / 12)), IF((Opportunity.Terms_Completed__c - FLOOR(Opportunity.Terms_Completed__c)) = 0, MONTH(TODAY()), CASE(MOD(((FLOOR((((YEAR( TODAY() ) * 12) + MONTH( TODAY() )) - ((YEAR(Opportunity.CloseDate) * 12) + MONTH(Opportunity.CloseDate))) / Opportunity.Contract_Length__c) + 1) * Opportunity.Contract_Length__c) + MONTH(Opportunity.CloseDate),12), 1,01, 2,02, 3,03, 4,04, 5,05, 6,06, 7,07, 8,08, 9,09, 10,10, 11,11, 12) ) , IF(AND(DAY(Opportunity.CloseDate) = 29,MONTH(Opportunity.CloseDate) = 02) , 28, IF(AND(DAY(Opportunity.CloseDate) = 31, OR(Opportunity.Next_Renewal_Month__c = 4,Opportunity.Next_Renewal_Month__c = 6,Opportunity.Next_Renewal_Month__c = 9,Opportunity.Next_Renewal_Month__c = 11)), 30, IF(AND(DAY(Opportunity.CloseDate) > 28, Opportunity.Next_Renewal_Month__c = 2), 28, DAY(Opportunity.CloseDate)) )) )} </td> </tr> </table>
I think some of the above formulas have a flaw in the Year calculation. If the end month is December, then it adds an extra year. To fix, you should subtract 1 from the current month. (Think of December as month 11 in a base-12 numerical system.) It works for the MOD too, but you need to add back the 1 after the formula.
Here's my formula:
DATE( YEAR ( Start_Date__c ) + FLOOR ( (MONTH ( Start_Date__c ) -1 + Contract_Term_Months__c )/12 ) ,
MOD ( MONTH ( Start_Date__c ) - 1 + Contract_Term_Months__c ,12) + 1,
DAY ( Start_Date__c )) - 1
Your formula may or may not need to subtract one day at the very end.
All,
Below formula works for adding months to a date, even for leap years
I have done many tests and compared the results to the results of excel calculations.
----
DATE (
/*YEAR*/
YEAR ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c - 1)/12),
/*MONTH*/
CASE ( MOD ( MONTH ( X01_MP_Start_Date__c )+MP_Number_Date__c, 12 ),0,12,MOD ( MONTH ( X01_MP_Start_Date__c )+MP_Number_Date__c, 12 )),
/*DAY*/
MIN ( DAY ( X01_MP_Start_Date__c ),
CASE ( MOD ( MONTH ( X01_MP_Start_Date__c )+MP_Number_Date__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 ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c)/12) , 400 ) = 0 || ( MOD ( YEAR ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c)/12) , 4 ) = 0 && MOD ( YEAR ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c)/12) , 100 ) <> 0
)
, 29,28)
,31 ) )
)
----
Please let me know what you think.
A