Taking advantage of the fact that end of month X is the day before the first of month (X + 1), and using MOD() to handle the month rolling over into the new year:
CASE(
Payment_Terms__c,
'Net', DATE( YEAR(TODAY()) + IF( MONTH(TODAY()) > 11, 1, 0), MOD( MONTH(TODAY()) + 0, 12) + 1, 1) - 1,
'Net 30', DATE( YEAR(TODAY()) + IF( MONTH(TODAY()) > 10, 1, 0), MOD( MONTH(TODAY()) + 1, 12) + 1, 1) - 1,
'Net 60', DATE( YEAR(TODAY()) + IF( MONTH(TODAY()) > 9, 1, 0), MOD( MONTH(TODAY()) + 2, 12) + 1, 1) - 1,
'Net 90', DATE( YEAR(TODAY()) + IF( MONTH(TODAY()) > 8, 1, 0), MOD( MONTH(TODAY()) + 3, 12) + 1, 1) - 1,
'Net 120', DATE( YEAR(TODAY()) + IF( MONTH(TODAY()) > 7, 1, 0), MOD( MONTH(TODAY()) + 4, 12) + 1, 1) - 1,
'30', TODAY() + 30,
'60', TODAY() + 60,
'90', TODAY() + 90,
TODAY()
)
So, for Net terms where we want the end of the current month, we find the start of next month and subtract 1 from it to get the end of this month:
YEAR(TODAY()) + IF( MONTH(TODAY()) > 11, 1, 0): next year if it's December, otherwise this year.MOD( MONTH(TODAY()) + 0, 12) + 1: next month, rolling from 12 to 1DATE( Y, M, 1) - 1: the last day of the previous month
The Salesforce date-handling routines take care of knowing what the previous month was (even if it's in the previous year) and how many days it has (even on leap years).
The following terms just repeat the same pattern.
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
Best Answer
If you're using a date field, it would be
YEAR(Field__c). However, if you're using a date/time field, you need to useDATEVALUE(e.g.YEAR(DATEVALUE(Field__c)). Finally, if you're trying to display it by itself, it needs to be a number return type. To make it back into a date, you'd have to useDATE: e.g.DATE(YEAR(DATEVALUE(Field__c)), 1, 1).