Error in formula field result

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….?

Answer

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 )

  )

)

Attribution
Source : Link , Question Author : KS Kumaar , Answer Author : crmprogdev

Leave a Comment