0

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 
) 
)
  • 1
    It seems like the only problem is you are adding 1 to year? What do you mean not working as expected? – Adrian Larson Mar 17 '16 at 23:28
  • I thought you'd posted before. Is this formula still broken? – Adrian Larson Jul 22 '16 at 17:59
0

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
  • Adrian -- a typo on the last example? 12-02-2015 sb 12-02-2016 – cropredy Mar 18 '16 at 0:26
  • Yep delicious copy pasta. @cropredy – Adrian Larson Mar 18 '16 at 0:45
0

There is a much easier way to do this: rather than trying to actually figure out which is the last day of the month, find out the first day of the next month and subtract a day:

IF(
  MONTH( MostRecent_Actual_Install_Date__c ) = 12,
  DATE( YEAR( MostRecent_Actual_Install_Date__c ), 12, 31 ),
  DATE( YEAR( MostRecent_Actual_Install_Date__c ), MONTH ( MostRecent_Actual_Install_Date__c ) + 1, 1 ) - 1 
)

This and more tips on working with dates and times in Salesforce formulas may be found in Using Date, Date/Time and Time Values in Formulas and Sample Date Formulas in Salesforce Help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.