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