Date field conversion to formula filed one year one month

Question

How can I build a formula to extract the one year one month iam implement the code that working some time but 31st date not working please any help me

IF( 
    AND(Month( Final_Cashback_Purchase_Date__c ) = 2,
        Day(Final_Cashback_Purchase_Date__c) = 29), 
    DATE(YEAR(Final_Cashback_Purchase_Date__c) + 1, 
        Month(Final_Cashback_Purchase_Date__c),
        Day(Final_Cashback_Purchase_Date__c)-1), 
    DATE(YEAR(Final_Cashback_Purchase_Date__c) + 1, 
        Month(Final_Cashback_Purchase_Date__c) + 1,
        Day(Final_Cashback_Purchase_Date__c)) 
)

Answer 1

Found this, have not tried it but seems like a solid answer:

DATE ( 

/*YEAR*/ 
YEAR ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c - 1)/12), 

/*MONTH*/ 
CASE ( MOD ( MONTH ( X01_MP_Start_Date__c )+MP_Number_Date__c, 12 ),0,12,MOD ( MONTH ( X01_MP_Start_Date__c )+MP_Number_Date__c, 12 )), 

/*DAY*/ 
MIN ( DAY ( X01_MP_Start_Date__c ), 
CASE ( MOD ( MONTH ( X01_MP_Start_Date__c )+MP_Number_Date__c,12 ) ,9,30,4,30,6,30,11,30,2, 

/* return max days for February dependent on if end date is leap year */ IF ( MOD ( YEAR ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c)/12) , 400 ) = 0 || ( MOD ( YEAR ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c)/12) , 4 ) = 0 && MOD ( YEAR ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c)/12) , 100 ) <> 0 
) 
, 29,28) 

,31 ) ) 
) 

https://developer.salesforce.com/forums/?id=906F00000008vsfIAA

Or This

IF(
  MOD( MONTH( date ) + 2, 12 ) = 2,
  IF(
    DAY( date ) > 28,
    DATE( YEAR( date ) + FLOOR( ( MONTH( date ) + 2 ) / 12 ), 3, 1 ),
    DATE( YEAR( date ) + FLOOR( ( MONTH( date ) + 2 ) / 12 ), 2, DAY( date ) )
  ),
  IF(
    OR(
      MOD( MONTH( date ) + 2, 12 ) = 4,
      MOD( MONTH( date ) + 2, 12 ) = 6,
      MOD( MONTH( date ) + 2, 12 ) = 9,
      MOD( MONTH( date ) + 2, 12 ) = 11
    ),
    IF(
      DAY( date ) > 30,
      DATE( YEAR( date ) + FLOOR( ( MONTH(  date  ) + 2 ) / 12 ),
       MOD( MONTH( date ) + 2, 12 ) + 1, 1 ),
      DATE( YEAR( date ) + FLOOR( ( MONTH( date ) + 2 ) / 12 ),
       MOD( MONTH( date ) + 2, 12), DAY( date ) )
    ),
    IF(
      MOD( MONTH( date ) + 2, 12 ) = 0,
      DATE( YEAR( date ) + FLOOR( ( MONTH( date ) + 2 ) / 12 ) - 1, 12, DAY( date ) ),
      DATE( YEAR( date ) + FLOOR( ( MONTH( date ) + 2 ) / 12 ),
       MOD( MONTH( date ) + 2, 12), DAY( date ) )
    )
  )
)

From https://success.salesforce.com/answers?id=90630000000hU5yAAE

Funny what a search will come up with...

Answer 2

Lets do some analysis to which day some last days of the month should be transferred. First cell -- Month and day from, second cell -- expected result month and day, last cell marks if date in previous cell does not exists.

||From Date|| To Date || Problem? ||
|| JAN 31  || DEC 31  || NO       ||
|| FEB 31  || JAN 31  || NO       ||
|| MAR 31  || FEB 31  || YES      ||
|| APR 31  || MAR 31  || NO       ||
|| MAY 31  || APR 31  || YES      ||
|| JUN 31  || MAY 31  || NO       ||
|| JUL 31  || JUN 31  || YES      ||
|| AUG 31  || JUL 31  || NO       ||
|| SEP 31  || AUG 31  || NO       ||
|| OCT 31  || SEP 31  || YES      ||
|| NOV 31  || OCT 31  || NO       ||
|| DEC 31  || NOV 31  || YES      ||

In this case, it seems that issue may appear only in 5 cases. That seems nice, because all them can be wrapped in nice IF formula.

DATE(
    YEAR(Final_Cashback_Purchase_Date__c) - 1, 
    MONTH(Final_Cashback_Purchase_Date__c) - 1, 
    IF( AND (MONTH(Final_Cashback_Purchase_Date__c) = 3,DAY(Final_Cashback_Purchase_Date__c) > 28),
          28,
          IF( AND (MONTH(Final_Cashback_Purchase_Date__c) = 5,DAY(Final_Cashback_Purchase_Date__c) > 30),
              30,
              IF( AND (MONTH(Final_Cashback_Purchase_Date__c) = 7,DAY(Final_Cashback_Purchase_Date__c) > 30),
                  30,
                  IF( AND (MONTH(Final_Cashback_Purchase_Date__c) = 10,DAY(Final_Cashback_Purchase_Date__c) > 30),
                      30,
                      IF( AND (MONTH(Final_Cashback_Purchase_Date__c) = 12,DAY(Final_Cashback_Purchase_Date__c) > 30),
                         30,
                         DAY(Final_Cashback_Purchase_Date__c)
                         )
                    )
                )
             )
    )
)

Two scenarios left. If previous year is Leap year, then in first case, we can still get 29 Feb instead of 28 February from 29 March, 30 March and 31 March. So, we need to do next adjustment -- if amount of days in february, previous year is 29 and input day is 29, 30 or 31 March -- then set it as 29 February. It can be done by next subformula:

IF( DAY(DATE( YEAR(Einde_DataDirect__c) - 1, MONTH(Einde_DataDirect__c), 28) + 1) = 29, 29, 28)

Second scenario -- January. In that case, we just need to subtract 2 years, and add 11 months in case of january.

As a result, there will be next formula:

DATE(
        IF (MONTH(Final_Cashback_Purchase_Date__c) = 1, YEAR(Final_Cashback_Purchase_Date__c) - 2, YEAR(Final_Cashback_Purchase_Date__c) - 1 ),
        IF (MONTH(Final_Cashback_Purchase_Date__c) = 1, MONTH(Final_Cashback_Purchase_Date__c) + 11, MONTH(Final_Cashback_Purchase_Date__c) - 1),
        IF( AND (MONTH(Final_Cashback_Purchase_Date__c) = 3,DAY(Final_Cashback_Purchase_Date__c) > 28),
              IF( DAY(DATE( YEAR(Final_Cashback_Purchase_Date__c) - 1, MONTH(Final_Cashback_Purchase_Date__c), 28) + 1) = 29, 29, 28),
              IF( AND (MONTH(Final_Cashback_Purchase_Date__c) = 5,DAY(Final_Cashback_Purchase_Date__c) > 30),
                  30,
                  IF( AND (MONTH(Final_Cashback_Purchase_Date__c) = 7,DAY(Final_Cashback_Purchase_Date__c) > 30),
                      30,
                      IF( AND (MONTH(Final_Cashback_Purchase_Date__c) = 10,DAY(Final_Cashback_Purchase_Date__c) > 30),
                          30,
                          IF( AND (MONTH(Final_Cashback_Purchase_Date__c) = 12,DAY(Final_Cashback_Purchase_Date__c) > 30),
                             30,
                             DAY(Final_Cashback_Purchase_Date__c)
                             )
                        )
                    )
                 )
        )
    )