1

I have created the Custom Field Field called DateTime1__c with type as (Date and Time) in order object.

When creating record in object, if i give value for DateTime1__c field, then another custom formula field called DateTime2__c is need to populate with the value after six months of DateTime1__c.

What formula i have to give while creating DateTime2__c to display the value after six months of DateTime1__c?

4
DATETIMEVALUE(TEXT(if((MONTH(DATEVALUE( DateTime1__c )) + 6)
<13,YEAR(DATEVALUE(DateTime1__c)),
YEAR((DATEVALUE(DateTime1__c))+1)))+"-"+
TEXT(if(MONTH(DATEVALUE(DateTime1__c))+6 <13,MONTH(DATEVALUE(
DateTime1__c ) ) + 6,MONTH(DATEVALUE(DateTime1__c )) - 6
))+"-"+TEXT(DAY( (DATEVALUE(DateTime1__c)))) +" "+TEXT(VALUE( MID(
TEXT( DateTime1__c  ), 12, 2 ) ))+":"+TEXT(VALUE( MID( TEXT(
DateTime1__c  ), 15, 2 ) ))+":"+TEXT(VALUE( MID( TEXT(
DateTime1__c  ), 18, 2 )) ))
  • while i am trying to get requirement using the above formula I am facing following error : Incorrect parameter type for function 'MONTH()'. Expected Date, received DateTime – user30475 Dec 30 '16 at 9:38
  • @cloudcomputinginsalesforce edit has been made it will work fine now – Manjot Singh Dec 30 '16 at 10:06
  • : Formula result is data type (Date), incompatible with expected data type (Date/Time). @Manjot Singh,I want output in Date and Time format – user30475 Dec 30 '16 at 10:09
  • @cloudcomputinginsalesforce i have changed type now – Manjot Singh Dec 30 '16 at 10:23
  • where is my Custom field - DateTime1__c in formula to add the value six months after – user30475 Dec 30 '16 at 10:29
2

You can write apex trigger on your object and in triggers u may use something like

DateTime d = DateTime.now();
DateTime d1 = d.addMonths(6);

hope this will help you.

  • I dont want apex class or apex trigger, I want Formula field only @Ashwani Kumar Thank you for your Guidance,I will put Vote for your answer for suggesting – user30475 Dec 30 '16 at 10:15
2

You can try with this formula

    if( MONTH(DATEVALUE( test_dt__c))> 6,  DATETIMEVALUE(TEXT(YEAR(DATEVALUE( test_dt__c))+1)+"-" +TEXT((MONTH(DATEVALUE( test_dt__c))+6)-12+ ( if(DAY( DATEVALUE( test_dt__c)) == 31 && MONTH(DATEVALUE( test_dt__c)) != 1&& MONTH(DATEVALUE( test_dt__c)) != 7, 1,0)  ))+"-" +TEXT(if(DAY( DATEVALUE( test_dt__c)) == 31 && (MONTH(DATEVALUE( test_dt__c)) == 1 ||  MONTH(DATEVALUE( test_dt__c)) = 7), DAY( DATEVALUE( test_dt__c)),1) )+" "+TEXT(VALUE( MID( TEXT( test_dt__c- 5 ), 12, 2 ) ))+":"+ MID( TEXT( test_dt__c- 5 ), 15, 2 )+":"+ MID( TEXT( test_dt__c- 5 ), 18, 2 ) ), 

DATETIMEVALUE(TEXT(YEAR(DATEVALUE( test_dt__c)) + if(DAY( DATEVALUE( test_dt__c)) == 31 && MONTH(DATEVALUE( test_dt__c)) != 1 && MONTH(DATEVALUE( test_dt__c)) != 7 && MONTH(DATEVALUE( test_dt__c)) == 6, 1,0)   )+"-" +TEXT(MONTH(DATEVALUE( test_dt__c))+ 6  +if(DAY( DATEVALUE( test_dt__c )) == 31 && MONTH(DATEVALUE( test_dt__c)) != 1 && MONTH(DATEVALUE( test_dt__c)) != 7, 1,0))   +"-" +      TEXT(if(DAY( DATEVALUE( test_dt__c )) == 31 && (MONTH(DATEVALUE( test_dt__c)) == 1 ||  MONTH(DATEVALUE( test_dt__c)) = 7), DAY( DATEVALUE( test_dt__c)),1) )   +" "+TEXT(VALUE( MID( TEXT( test_dt__c - 5 ), 12, 2 ) ))+":"+ MID( TEXT( test_dt__c- 5 ), 15, 2 )+":"+ MID( TEXT( test_dt__c- 5 ), 18, 2 ) ))

Where date/time is your field and TZoffset is the difference between the user’s time zone and GMT.

This is the working example I checked in my org.

Refernce

  • while saving this formula field it is showing error like :Field TZoffset does not exist. Check spelling. How I can use this TZoffset @Tushar sharma – user30475 Dec 30 '16 at 9:30
  • @cloudcomputinginsalesforce TZoffset is the time difference read reference document for more information. – Tushar Sharma Dec 30 '16 at 9:51
  • yes I am reading your reference, while i am reading this reference,I unable to get what exactly TZoffset is? Please provide an example how to use it – user30475 Dec 30 '16 at 9:57
  • @cloudcomputinginsalesforce for testing take any integer like 1,2 or 3. in real world if your Timezone is GMT+5 so 5 is the number you need. – Tushar Sharma Dec 30 '16 at 10:00
  • @cloudcomputinginsalesforce check my updated answer you might need to adjust TZoffset – Tushar Sharma Dec 30 '16 at 10:31
0

Seems like you want to achieve this using customization not by apex code - You can do by using workflow rule. The example is based on following requirement but you can modify for your use case -

The customer has a start date (in the example below, it is called "Effective date") and has a number field called Policy term which is a piclikst that gives the number of month an opportunity runs for (in the example below, the values were 1 Month, 6 Months and 12 Months).

He wants to add the policy term to the start date in order to calculate the "Renewal date".

This solution will even work on leap years !

The solution is listed on salesforce help articles - https://help.salesforce.com/articleView?id=000002391&language=en_US&type=1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy