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
LaaralLaaral 

Formula which counts date from months

Hi, I have a problem with my formula which should count End Date from Start Date + Contract Term, but the problem is that Contract Term values are not always divisible by 12? For example (37 or 41) .

Here is my formula before I found out that all the values aren't divisible by 12:

DATE(
(YEAR(Service_Contract_Period_Start_Date__c)+VALUE(TEXT(Service_Contract_Term__c ))/12),
MONTH(Service_Contract_Period_Start_Date__c),
DAY(Service_Contract_Period_Start_Date__c)
)

How should I solve this ?
Ramu_SFDCRamu_SFDC
Can you please provide more details on the output you are expecting so that I can suggest you some alternatives. The below article might also help

https://help.salesforce.com/HTViewHelpDoc?id=formula_using_date_datetime.htm&language=en_US
LaaralLaaral
I'm expecting the Ending date (DATE value like in above formula) from a formula where I count year from Start Date + Contract Term / 12 (Normally MONTHS would be divided by 12 but in some contracts the term lenght is 37 or 41 so it can't do the dividing by 12)
AgiAgi
Hi,

you can put
Service_Contract_Period_Start_Date__c + Service_Contract_Term__c*30,
or try this:

Date(
Year(Service_Contract_Period_Start_Date__c) + FLOOR(Service_Contract_Term__c /12)
+
if( month(Service_Contract_Period_Start_Date__c)+ mod(Service_Contract_Term__c ,12)>12,
(month(Service_Contract_Period_Start_Date__c)+ mod(Service_Contract_Term__c ,12))-12, 0),

if( ( month(Service_Contract_Period_Start_Date__c)+ mod(Service_Contract_Term__c ,12) )>12,
mod ( month(Service_Contract_Period_Start_Date__c)+ mod(Service_Contract_Term__c ,12) ,12),
month(Service_Contract_Period_Start_Date__c)+ mod(Service_Contract_Term__c ,12)),

if( AND(
Day (Service_Contract_Period_Start_Date__c )>28,
Month(Service_Contract_Period_Start_Date__c + Service_Contract_Term__c  *30)=2), 28,

if( AND( Day (Service_Contract_Period_Start_Date__c )>30,
OR( Month(Service_Contract_Period_Start_Date__c + Service_Contract_Term__c  *30)=4,
Month(Service_Contract_Period_Start_Date__c + Service_Contract_Term__c  *30)=6,
Month(Service_Contract_Period_Start_Date__c + Service_Contract_Term__c  *30)=9,
Month(Service_Contract_Period_Start_Date__c + Service_Contract_Term__c  *30)=11)), 30,

Day (Service_Contract_Period_Start_Date__c ))))