ShowAll Questionssorted byDate Posted
waylonatcim

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

waylonatcim

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

Buell
You may need to add some additional logic for your DAY formula to account for differing month lenghts and leap years.
waylonatcim

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.

Buell
Had the same problem, good luck getting around it as SFDC wont budge on their formula sizes.  After months of trying to simplify I ended up settling with an s-control, no limits to size, but you can't use it for reporting.  Let me know if there is interest, I'll post it here.
Jakester
It's not exactly the same thing, but this formula has been rock solid for me for subtracting months and then returning the first day for the right month. Hope it can be of some use.

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 )

Buell
Looks familiar.  Here is another version which will add months and return the first day:

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 )

Buell

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;"> &nbsp;&nbsp;&nbsp;&nbsp;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>

Darren_Bronson

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.

Message Edited by Darren_Bronson on 11-06-2009 11:36 AM
ben_sda

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

Les_Stacey
This works a treat. thanks very much.