[SalesForce] Updating a Date Field with Last Day of The Month based on another Date Field

Updating a Date Field with Last Day of The Month based on another Date Field.

I need to write a formula to do a field update when an Implementation is complete.

Basically once it's marked as complete I need a field update that will set a date field called Billing_To_Begin_Date__c with the last day of the month from another field called MostRecent_Actual_Install_Date__c.

For example, the implemntation is marked complete, the MostRecent_Actual_Install_Date__c = March 2, 2016 then the Billing_To_Begin_Date__c becomes March 31, 2016

Can anyone help me accomplish this? I have the WFR created but need help with the formula to set that last day of the month. Below is what I had but it is not working as expected: 

DATE 
( 
YEAR( MostRecent_Actual_Install_Date__c ) + 1, 
Month( MostRecent_Actual_Install_Date__c ), 
CASE( MONTH(MostRecent_Actual_Install_Date__c), 
1, 31, 
2, IF( MOD( YEAR(MostRecent_Actual_Install_Date__c) + 1, 4) = 0,29, 28), 
3, 31, 
4, 30, 
5, 31, 
6, 30, 
7, 31, 
8, 31, 
9, 30, 
10, 31, 
11, 30, 
12, 31, 
0 
) 
)

Best Answer

Your formula works fine for me. Just replace all instances of

YEAR(MostRecent_Actual_Install_Date__c) + 1

with

YEAR(MostRecent_Actual_Install_Date__c)

Sample input/output combos:

03-02-2016 / 03-31-2016
02-02-2016 / 02-29-2016
01-02-2016 / 01-31-2016
12-02-2015 / 12-31-2015
Related Topic