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.
So if I understand correctly, the requirement is this:
A batch will run in the background every hour which will update the Contract field on the lead. When this gets update, the Contract Date field should then be updated with date (TODAY) of when the Contract field gets populated.
So the steps to take are:
- create a workflow rule on Lead, triggering only on "created, and any time it’s edited to subsequently meet criteria". Criteria should be: Contract not equal to ''.
- add a field update to the workflow rule. The field update should update the Contract_Date field to value TODAY() (use the formula editor to set that value).
That should do it. The workflow will run only once (when the Contract field goes from empty to non-empty).
Best Answer
You can create formula fields as follows
Month
Year
Quarter