 ShowAll Questionssorted byDate Posted khayum33

# Revenue schedules - formula for actual amount per month

Greetings All,

Long time user, first time post. Looking for some help with customization of revenue schedule formulas… The default divides the \$ by # of months. I need the actual monthly amount - even if there's only 1 day remaining in the month an ad starts.

Scenario: Advertising Sales co. – Opptys/products to display the amount of revenue, by month, for the Ad term. In other words: actual “ad run” \$ amount, per month. Also, the number of days/months an ad will run vary. Can be 1-day to 26-months...

Here is a simple example:

• Amount Closed \$12,000.00
• Ad start date 8/15/2010
• Ad end date 10/20/2010
• Total of 65 days @ 184.62 per day

# of days the ad will run in each month is:

• 16 days, first month (8/15/2010 -8/31/2010). 16*184.62= What I need displayed is: 2,953.85 August.

• 30 days, second month (9/1/2010-9/30/2010)30*184.62= "    "  5,538.46 September.

• 19 days, third month (10/1/2010-10/20/2010). 19*184.62= "    " 3,507.78 October.

ANY help is greatly appreciated! I’ve spent countless hours on the discussion boards and developer pages looking for this solution... no luck (found similar postings; not resolved).

Thank you!

Karen Satya.Kona

It can only be a formula field or a trigger too will do? Satya.Kona

Lol....I wrote some poor function here and ran out of length (more than 5000 chars)...Gave up...will keep checking for

solution here..

CASE(
MONTH( ad_start_date__c ) ,
1,'\$ ' &TEXT(ROUND((31- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' JAN',
2,'\$' & TEXT(ROUND((28- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' FEB',
3,'\$ ' &TEXT(ROUND((31- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' MAR',
4,'\$ ' &TEXT(ROUND((30- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' APR',
5,'\$ ' &TEXT(ROUND((31- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' MAY',
6,'\$ ' &TEXT(ROUND((30- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' JUN',
7,'\$ ' &TEXT(ROUND((31- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' JUL',
8,'\$ ' &TEXT(ROUND((31- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' AUG',
9,'\$ ' &TEXT(ROUND((30- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' SEP',
10,'\$ ' &TEXT(ROUND((31- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' OCT',
11,'\$ ' &TEXT(ROUND((30- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' NOV',
12, '\$ ' &TEXT(ROUND((31- DAY(ad_start_date__c)) * amt_per_day__c,2)) & ' DEC',
null)
&
BR()
&
CASE(
1,'\$ ' &TEXT(ROUND((31- DAY(ad_end_date__c)) * amt_per_day__c,2)) & ' JAN',
2,'\$' & TEXT(ROUND((28- DAY(ad_end_date__c)) * amt_per_day__c,2)) & ' FEB',
3,'\$ ' &TEXT(ROUND((31- DAY(ad_end_date__c)) * amt_per_day__c,2)) & ' MAR',
null) DESTIN

Hi

I got close to what you wanted by creating a field for each of the months

if the month fell within the start and end range, then the value would be the number of

days in that month. (that can be calculated by a formula field or using a workflow update)

(you will also need a field that calculates if the start and end year are the same)

divide ad amount by the total days and then use your daily amount to calculate each months' value

(leap years may be an issue) APPropoz User
I am curious if you were able to resolve this in a satisfactory manner? Asma Erum
Was anyone able to resolve this?