3

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.

  • This is definately something that SteveMo over at the SF Success Çommunity could answer. Too bad we can't just tag him here for this :) – akarnid Sep 2 '13 at 10:30
  • I'm confused. How is "Net 30" different than "30"? Why can't you just take TODAY() + 120 for "Net 120"? My understanding is that "Net X" means "balance due X days from invoice date". – tomlogic Sep 5 '13 at 4:12
  • 1
    Ah, I see now. en.wikipedia.org/wiki/Net_D explains it. In your case, "Net 30" means at the end of the following month... – tomlogic Sep 5 '13 at 4:21
2

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.

| improve this answer | |
  • I love the idea, but this also must be tweaked to support leap years. – Saariko Sep 5 '13 at 5:27
  • @Saariko, take a closer look. I've fixed and tested this formula, including removing an errant close parenthesis. By getting the date of the first of the following month and then subtracting 1 (DATE(y, m, d) - 1), the Salesforce date functions will take care of leap year for us. I guess you could create a version with TODAY() replaced by another field and test further, but I'm not seeing how this method fails for leap year since it doesn't consider the number of days in a month. – tomlogic Sep 5 '13 at 15:58
  • ill check tomorrow, as something doesn't look right. As for leap year: If I am on January, and Net + 30 : answer should be : February 29. I don't see how your formulae address this – Saariko Sep 5 '13 at 16:38
  • 1
    DATE( 2016, MOD( 1 + 1, 12) + 1, 1) - 1 = DATE( 2016, 3, 1) - 1 = "one day before March 1, 2016" = "February 29, 2016" – tomlogic Sep 5 '13 at 16:45
  • I would leave a thank you comment, if I knew it wouldn't get deleted. :-) SPEECHLESS !!!! – Saariko Sep 5 '13 at 18:23

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.