function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
alachmannalachmann 

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

 

Message Edited by alachmann on 03-21-2009 03:02 PM
Best Answer chosen by Admin (Salesforce Developers) 
alachmannalachmann

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

MarkSilberMarkSilber
This is from the Salesfore online help and should help with the February date issue. Not an easy solution, but it should work.

 

 

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

 

 

 

 

fifedogfifedog
does the date have to be excatlcy 1 month from the date? you could just use the date + 30 which will add 30 days to your date. 
alachmannalachmann

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

alachmannalachmann

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 )

 

This was selected as the best answer