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?

Improve this question

4 Answers 4

Reset to default
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 )) ))
Improve this answer
9
  • 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, 2016 at 9:38
  • @cloudcomputinginsalesforce edit has been made it will work fine now
    – Manjot Singh
    Dec 30, 2016 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, 2016 at 10:09
  • @cloudcomputinginsalesforce i have changed type now
    – Manjot Singh
    Dec 30, 2016 at 10:23
  • where is my Custom field - DateTime1__c in formula to add the value six months after
    – user30475
    Dec 30, 2016 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.

Improve this answer
1
  • 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, 2016 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

Improve this answer
10
  • 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, 2016 at 9:30
  • @cloudcomputinginsalesforce TZoffset is the time difference read reference document for more information.
    – Tushar Sharma
    Dec 30, 2016 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, 2016 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, 2016 at 10:00
  • @cloudcomputinginsalesforce check my updated answer you might need to adjust TZoffset
    – Tushar Sharma
    Dec 30, 2016 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

Improve this answer

You must log in to answer this question.