+ Start a Discussion

Need Help with YTD Quota Formula

I need a custom formula field in the User object to calculate YTD Quota per person. So far, every formula I try runs into the "No Formula >5,000 Characters" rule, or it's so simple that too much falls through the cracks. Can anyone think of a way to simplify?


The rules are deceptively simple...


1) Each rep can have a different annual quota, split into a monthly goal.

2) Each rep gets two months "free" after their hire date before quotas start accumulating. If hired after the 15th of the month, they get that month "free" too.

3) Our fiscal year goes from 2/1 to 1/31

4) I've been determining the current fiscal month being reported by extracting the month from today's date,  and if it's January, setting the fiscal month # to 11 (December), or if it's February, setting the fiscal month # to 12, otherwise taking the month number - 2.


I've been approaching this by trying to capture the date the quota starts, then using that to determine where in the fiscal year this falls, and then determine how many months they get "free" that year versus the number of months elapsed so far this year.  But like I said, that's running into the character limit. Ultimately, I've been trying to find a way to capture the # of months I should multiply by the monthly quota (bold, italic, underlined below). But if someone can think of another way to go about it, I'm all ears.


I'm probably missing something terribly obvious, so I'm hoping a fresh set of eyes will see something that I'm overlooking because I'm trying to be too complicated.




Rep A

Hire Date  4/1/06

Date Quota Starts  6/1/06 (far prior to current FY)

Annual Quota  $500,000

Monthly Quota  $41,666.67

Current Reporting Month:  November 2009, which is fiscal month #10 of the current year

YTD Quota should be $416,667 or Monthly Quota *10 (same as fiscal month #)


Rep B

Hire Date  5/1/09

Date Quota Starts  7/1/09

Annual Quota  $600,000

Monthly Quota  $50,000

Current Reporting Month - November, fiscal month #10

YTD Quota should be $500,000 or Monthly Quota *5 (this is where I get complicated, trying to capture the month of the Date Quota Started and adjusting to the fiscal month by -2 if it's March-December, or forcing if it's Jan or Feb)


Rep C

Hire Date 11/28/09

Date Quota Starts  1/28/10

Annual Quota $480,000

Monthly Quota $40,000

Current Reporting Month - November, fiscal month #10

YTD Quota should be $0 or Monthly Quota * 0 , since the Date Quota Starts > the start of the current fiscal month  (and this is where I've totally failed, since this goes into another year, and the month has to be forced because it's calendar month #1, but fiscal month #12)


Ideas? Suggestions? Anything? I'm desperate!

did you find the solution for this? I am striggling with the same. 
Unfortunately, no. I had to give up and get something done in Excel. Sorry!
I am struggling with similar thing. I need this quota amt to be flexible moving up as we progress towards the year end. I mean It cannot be just 1.25 as we are in current second quarter it should show the increment of few days added to the quota amt for every rep. Static quota amt is not serving any purpose. Btw, what formula you used in Excel? Thanks for listening. This message was secured by Zix(R).