function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
TTATTA 

Date Calculation

Hi all,

 

I have got the following formula which gives me a date which is 6 months earlier than my start date:

DATE(YEAR( Club_Start_Date__c ),MONTH( Club_Start_Date__c ) - 6,DAY( Club_Start_Date__c )) 

 

It works perfectly fine if the start date is from July to Dec otherwise it produces an error message.

 

I tried this work around Club_Start_Date__c - (From__c * 30) , although it works, it doesn't procude the exact (correct) date that I am after.(From_c = 6)

 

I wonder if you guys have a better work around. 

 

Many thanks, 

MLamb2005MLamb2005

Hi there,

 

Welcome to the boards!

 

The error you're getting is due to a negative number in the Month parameter.  For example, if it was April the formula you posted would evaluate 4 - 6 and place -2 as the month.

 

I would approach this with an IF statement and check if the Month is more than 6.  If so, your calculation works fine.  If not, you need to subtract one from the year and actually add 6 to the Month parameter. I haven't tested this, but it would look something like:

 

IF(MONTH(Club_Start_Date__c) > 6, DATE(YEAR( Club_Start_Date__c ),MONTH( Club_Start_Date__c ) - 6,DAY( Club_Start_Date__c )), DATE(YEAR(Club_Start_Date)-1, MONTH( Club_Start_Date__c ) + 6,DAY( Club_Start_Date__c )) )

 

Hope that helps!

Matt

TTATTA

Hi there,

 

Thanks for your reply. The variable 6 is not a fixed number. I get that from From_c field. Besides the range can be from 1month to 36 months rather than 1 to 12 months.

 

Thanks for your input though.

 

Regards,