Apex Code Development You need to sign in to do that
Don't have an account?
Sumsum2253 Help please! Date Formula???
Hi Guys,
I am trying to create a formula in a custom field ("Pipeline_(CloseDate)__c") that will calculate the "%" of the revenue (in £'s) from the "Amount" field based on the date in the "CloseDate" field on an Opportunity!
The aim is to show the revenue value that will be realized in the currency financial year based on the "CloseDate"?
Example:-
If - "Amount" = £1,000,000 & "CloseDate" = 01/07/2011... Then the value in "Pipeline (CloseDate)" must be "£500,000"!
The "Pipeline (CloseDate)" value obviously needs to adjust accordingly, as and when the date in the "CloseDate" field is changed.
I seem to have hit a brick wall with this so open to any suggestions please?
Many thanks
Sorry about that. I tested this version and it does work.
Amount__c - (Amount__c*((CloseDate__c-Date(year(today()),1,1))/365))
One thing to remember though is that this calculates based on the number of days that have passed, so in your example from above, the data would calculate like this:
1/7/11 is the 182nd day of the year
182/365 ~ .4986
.4986*1,000,000,000 = 498,639,136.99 would be the amount for the time that has passed.
1,000,000,000 - 498,639,136.99 = 501,369,863.01 remaining this year
so essentially we are not quite halfway through the year so the value is not quite half, but is exact.
Also you will note that I added a bit, this was because I realized that we were calculating how much we had used so far, in order to get how much was remaining in the year we had to subtract that from the total amount.
All Answers
Hi,
I'm not able to spot the logic that you've used to calculate the value in the "Pipeline (CloseDate)" . How did you arrive at
If - "Amount" = £1,000,000 & "CloseDate" = 01/07/2011... Then the value in "Pipeline (CloseDate)" must be "£500,000"!
If you could throw some light into the details of this calculation, then I guess I can provide the generalised formula for this.
do you want formula for these values that you mentioned in your post or they are only example and there is some business logic to determine the formula condition.
Hi Jerun,
The "Pipeline (CloseDate)" is a custom formula field and is the "Target Field" for the output of the formula.
The Values in the example are purely fictional and only show what the desired output needs to be in the "Target Field" if the "Amount = £1,000,000" and the "CloseDate = 01/07/2011", then the "Target Field" value should equate to "£500,000"
The formula needs to evaluate what the revenue will be in the remainder of the current fiscal year based on the date selected in the "CloseDate" field?
Hope this helps/ Makes sense?
Thanks for your help
This is how far I got with this...not sure if im barking up the wrong tree or not but it doesnt seem to throw out the right figures?
Hope this helps?
So essentially what you are saying is that 1/7/2011 is halfway through your fiscal year? And you want a formula that will calculate what percentage of income will remain in the fiscal year?
so what you want is:
amount*((CloseDate-Date(1,1,year(today()))/365)
You can add the math to account for a leap year. Essentially it calculates the percentage of the year remaining and multiplies the amount by that percentage.
My understanding is also the same as Jake's.
Please let us know if you are expecting something different
Thanks for this Jake but I keep getting a syntax error on the formula you supplied?
- Error: Syntax error. Missing ')' -
I think you are on the right track though...
Basically we record Opportunities with an annual revenue value which we record in the amount field.
The "Pipeline (CloseDate)" needs to automatically calcualte what percentage of the annual revenue will be realized in the current fiscal year based on whatever "close date" the User selects, indicating what date of the current year this annual contract will begin?
Sorry i was just missing a ')' Here is what I believe that it should look like:
amount*((CloseDate-Date(1,1,year(today())))/365)
No probs Jake.
Ive just tried your formula again and this time there are no syntax errors but the field itself simply displays "#Error!"?
dont really understdand why it does this when the "Check syntax" says its fine?!?!
Sorry about that. I tested this version and it does work.
Amount__c - (Amount__c*((CloseDate__c-Date(year(today()),1,1))/365))
One thing to remember though is that this calculates based on the number of days that have passed, so in your example from above, the data would calculate like this:
1/7/11 is the 182nd day of the year
182/365 ~ .4986
.4986*1,000,000,000 = 498,639,136.99 would be the amount for the time that has passed.
1,000,000,000 - 498,639,136.99 = 501,369,863.01 remaining this year
so essentially we are not quite halfway through the year so the value is not quite half, but is exact.
Also you will note that I added a bit, this was because I realized that we were calculating how much we had used so far, in order to get how much was remaining in the year we had to subtract that from the total amount.
Spot on Jake, nice one buddy, you're a star...
It doesn't need to be exact so 1 or 2 days difference wont be any problem.
Only thing I had to change was the field titles as the "amount" & "CloseDate" are not custom fields:-
"Amount - (Amount*((CloseDate-Date(year(today()),1,1))/365))"
But this works perfectly now...
I've even been able to take this a step further and show the revenue value for the "Next fiscal year" by adding another custom field "Nxt YR Pipeline (date)" with a formula to deduct the output of your formula from the original revenue amount.
This exceeds the original requirements on this.
I would say I owe you one but I have the feeling you would'nt need my help with anything ;-)
Thanks Jake
Good deal, glad to be of assistance!