Apex Code Development You need to sign in to do that
Don't have an account?
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
It can only be a formula field or a trigger too will do?
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(
MONTH( ad_end_date__c) ,
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)
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)