Apex Code Development You need to sign in to do that
Don't have an account?
alachmann How to catch error in a formula or how to add a number of months to a date to build a new date
Hello
I have a quite easy formula to build but I get stuck
I have a date field. I have a number field (number of months)
I am trying to add the number of month to the date field to get a new date
For that I have rebuild date thank to month, year, day of date field and date function
Issue I have for instance is : 01/30/09 + 1 month -> date (2009,02,30) which gives an error
Is there a way to say : if (date(2009,02,30) is an error) then .... ?
Is there another way to build my calculated date ?
Thanks for your help
Regards
Works well
Little error in formula :
FORMULA FIELD 3: Hide on page layout
Field Name: Expiration Day
Data Type: Formula (Text)
Formula:
LPAD ( TEXT (
IF ( AND ( Expiration_Month__c = "02" , DAY( StartDate ) > 28 ) , 28 , IF ( AND ( DAY( StartDate ) = 31, OR ( Expiration_Month__c="04", Expiration_Month__c="06", Expiration_Month__c="09", Expiration_Month__c="11" ) ) , 30 , DAY( StartDate )
)
)
), 2, "0" )
It would be quite easy to deal with bissextile year but then formula is too long :smileytongue:
AND ( Expiration_Month__c = "02" , DAY( StartDate ) > 28, MOD(VALUE(Expiration_Year__c)-2000,4) <>0 )
All Answers
Here is a method to determine and display the expiration or end date of a record with a start date and a number of months field. This requires all the following formula fields. The end result is a displayed DATE-type field showing the Expiration. Assumptions: - Object has a DATE field called StartDate - Object has a NUMBER field called Number_of_Months__c - Month means calendar month, not 30-day month - Ignores Feb 29 Example: Campaign starting October 21, 2008 and lasting 3 months would end Jan 20, 2009 (Jan 21 would be the beginning of a 4th month). FORMULA FIELD 1: Hide on page layout Field Name: Expiration Year Data Type: Formula (Text) Formula: TEXT ( YEAR( StartDate ) + FLOOR( ( MONTH( StartDate ) - 1 + Number_of_Months__c ) / 12 ) ) FORMULA FIELD 2: Hide on page layout Field Name: Expiration Month Data Type: Formula (Text) Formula: LPAD ( TEXT ( IF ( MOD( MONTH( StartDate ) + Number_of_Months__c, 12 ) = 0 , 12 , MOD ( MONTH( StartDate ) + Number_of_Months__c , 12 ) ) ), 2, "0" ) FORMULA FIELD 3: Hide on page layout Field Name: Expiration Day Data Type: Formula (Text) Formula: LPAD ( TEXT ( IF ( AND ( Expiration_Month__c = "2" , DAY( StartDate ) > 28 ) , 28 , IF ( AND ( DAY( StartDate ) = 31, OR ( Expiration_Month__c="4", Expiration_Month__c="6", Expiration_Month__c="9", Expiration_Month__c="11" ) ) , 30 , DAY( StartDate ) ) ) ), 2, "0" ) FORMULA FIELD 4: Display on page layout Field Name: Expiration Date Data Type: Formula (Date) Formula: DATEVALUE( Expiration_Year__c & "-" & Expiration_Month__c & "-" & Expiration_Day__c ) - 1
yes it is really one month so sometimes 30 days or 31 days or 28 days or 29 days
so I believe that complex solution would give me the answer (it is exactly my need) but I have to try it first to see how it works although 3 invisible fields is not what I was waiting for just to perfom my needs
Works well
Little error in formula :
FORMULA FIELD 3: Hide on page layout
Field Name: Expiration Day
Data Type: Formula (Text)
Formula:
LPAD ( TEXT (
IF ( AND ( Expiration_Month__c = "02" , DAY( StartDate ) > 28 ) , 28 , IF ( AND ( DAY( StartDate ) = 31, OR ( Expiration_Month__c="04", Expiration_Month__c="06", Expiration_Month__c="09", Expiration_Month__c="11" ) ) , 30 , DAY( StartDate )
)
)
), 2, "0" )
It would be quite easy to deal with bissextile year but then formula is too long :smileytongue:
AND ( Expiration_Month__c = "02" , DAY( StartDate ) > 28, MOD(VALUE(Expiration_Year__c)-2000,4) <>0 )