[SalesForce] How to add days to a date field that spans over a year

I have a field called: Payment terms ( holds values as: 30, 60, Net 30, Net 60..) which defines that payment due date of an oppty.

Payment due date is a Date field, with default value of: Today().

I have a workflow rule and field update that will update the payment due date upon selection on the payment terms field:

CASE( Payment_Terms__c , 
"Net"    ,DATE(YEAR(TODAY()),MONTH(TODAY()),CASE( MONTH(TODAY())+1,1, 31, 2, IF( MOD( YEAR(TODAY()), 4) = 0, 29, 28), 3, 31, 4, 30, 5, 1, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31, 0)), 
"Net 30" ,DATE(YEAR(TODAY()),MONTH(TODAY())+1,CASE( MONTH(TODAY())+1,1, 31, 2, IF( MOD( YEAR(TODAY()), 4) = 0, 29, 28), 3, 31, 4, 30, 5, 1, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31, 0)),
"Net 60" ,DATE(YEAR(TODAY()),MONTH(TODAY())+2,CASE( MONTH(TODAY())+2,1, 31, 2, IF( MOD( YEAR(TODAY()), 4) = 0, 29, 28), 3, 31, 4, 30, 5, 1, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31, 0)),
"Net 90" ,DATE(YEAR(TODAY()),MONTH(TODAY())+3,CASE( MONTH(TODAY())+3,1, 31, 2, IF( MOD( YEAR(TODAY()), 4) = 0, 29, 28), 3, 31, 4, 30, 5, 1, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31, 0)),
"Net 120",DATE(YEAR(TODAY()),MONTH(TODAY())+4,CASE( MONTH(TODAY())+4,1, 31, 2, IF( MOD( YEAR(TODAY()), 4) = 0, 29, 28), 3, 31, 4, 30, 5, 1, 6, 30, 7, 31, 8, 31, 9, 30, 10, 31, 11, 30, 12, 31, 0)),
"30"     ,TODAY()+30,
"60"     ,TODAY()+60,
"90"     ,TODAY()+90,
TODAY())

The problem I face now, is that once the date is in Next year e.g: October with payment terms of Net 90 – needs to be January first 2014.

Trying the line:

"Net 120", DATE(YEAR(TODAY()),MONTH(TODAY())+4,CASE( MONTH(TODAY())+4,1, 31, 2, IF( MOD( YEAR(TODAY()), 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)),

gives me an error:

enter image description here

A workflow or approval field update caused an error when saving this
record. Contact your administrator to resolve it. Payment Due Date:
value not of required type: common.formula.FormulaEvaluationException:
Month or Day out of range in DATE() function

I am sure I need to overcome the rolling year issue, not sure how to do that.

Best Answer

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 1
  • DATE( 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.

Related Topic