5

My requirement is exactly looking like This question. The question has solution almost except in one case.

The issue is, If we select 31st march, Then the result should like 31st march+ 6 months, which means 31st September. But we do not have this date(31st sep). So, then the result should be 1st October. But in this its giving Error as in result. This is giving error in all 31st dates except January 31st. (Beacuse 31st January + 6 months is equal to July 31st ).

The formula is given below.

if( MONTH(DATEVALUE( CreatedDate ))> 6,  DATETIMEVALUE(TEXT(YEAR(DATEVALUE( CreatedDate ))+1)+"-" +TEXT((MONTH(DATEVALUE( CreatedDate ))+6)-12)+"-" +TEXT(DAY( DATEVALUE( CreatedDate )))+" "+TEXT(VALUE( MID( TEXT( CreatedDate - 5 ), 12, 2 ) ))+":"+ MID( TEXT( CreatedDate - 5 ), 15, 2 )+":"+ MID( TEXT( CreatedDate - 5 ), 18, 2 ) ), DATETIMEVALUE(TEXT(YEAR(DATEVALUE( CreatedDate )))+"-" +TEXT(MONTH(DATEVALUE( CreatedDate ))+ 6)+"-" +TEXT(DAY( DATEVALUE( CreatedDate )))+" "+TEXT(VALUE( MID( TEXT( CreatedDate - 5 ), 12, 2 ) ))+":"+ MID( TEXT( CreatedDate - 5 ), 15, 2 )+":"+ MID( TEXT( CreatedDate - 5 ), 18, 2 ) ))

Please suggest me, How to resolve the error....?

2 Answers 2

2

The below is taken from Sample Date Formulas in the Using Date and Date/Time Formulas Tip Sheet.

This formula does the following:

  • Returns March 1 if the future month is a February and the day is greater than 28. This portion of the formula performs the same for both leap and non-leap years.
  • Returns the first day of the next month if the future month is April, June, September, or November and the day is greater than 30.
  • Otherwise, it returns the correct date in the future month.

This example formula adds two months to a given date. You can modify the conditions on this formula if you prefer different behaviors for dates at the end of the month.

You can easily modify what's below to give you what you want for 6 months or any other number of months between two dates with the behavior that you want to see.

  DATE(

  YEAR( ***date*** ) + FLOOR( ( MONTH ( date ) + 2 - 1 ) / 12 ),

  MOD( MONTH ( date ) + 2 - 1 + 
    IF( DAY ( date ) > CASE( MOD( MONTH( date ) + 2 - 1, 12 ) + 1, 

      2, 28,

      4, 30,

      6, 30,

      9, 30, 

      11, 30,

      31 ), 1, 0 ), 12 ) + 1,

    IF( DAY( date ) > CASE( MOD( MONTH( date ) + 2 - 1, 12 ) + 1,

      2, 28, 

      4, 30, 

      6, 30, 

      9, 30, 

      11, 30, 

      31 ), 

    1, DAY( date )

  )

)
8
  • 1
    I tried with the above formula by modifying date with my input date, as usual its giving error like incorrect parameter type for function 'YEAR()'. Expected Date, received DateTime....
    – KS Kumaar
    Commented Jan 3, 2017 at 12:33
  • 1
    Anyplace you see Date in the above formula, you will need to use DATEVALUE( CreatedDate ) as you did in your original formula in your question. CreatedDate is a Date-Time, not a Date Value.
    – crmprogdev
    Commented Jan 3, 2017 at 12:50
  • I have changed as your instructions, but giving Error: Formula result is data type (Date), incompatible with expected data type (Date/Time).
    – KS Kumaar
    Commented Jan 3, 2017 at 13:12
  • please note that, i want result in Date-Time format
    – KS Kumaar
    Commented Jan 3, 2017 at 13:13
  • Just use DateTimeValue(DateResult) to convert it back. It will then display in the user's local time zone.
    – crmprogdev
    Commented Jan 3, 2017 at 13:19
1

You can use this formula I have handle that case of 31 days here test_dt__c is a test field which you can replace with your own fields.

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 ) ))

PS: In this formula I have not handle the case of February and leap year. Which I will leave OP to update.

3
  • Yes, @Tushar Its not handled with February and leave years..... can you help me in handling with February and leave years..?
    – KS Kumaar
    Commented Jan 2, 2017 at 11:14
  • @KSKumaar there are many use case which we need to handle. My suggestion you should go with apex trigger. So you can do this in just 2 lines. Commented Jan 2, 2017 at 11:33
  • i am unable to get the requirement using formula provided by crmprogdev. Couls you help in this?
    – KS Kumaar
    Commented Jan 3, 2017 at 12:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .