function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Ivan WinzerIvan Winzer 

calculate amount based on date

Ok so this may sound crazy and may not be able to be done using formula's but hopefully it can.

So i have a field called wine credit avalible which will show user how much money the customer has left thru our buyin period (i.e. Started off with $750, then used $700, now has $50 left). So here is where it gets compliacated. Our buyin period stars on 5/1 each year so the current one would be from 5/1/15 to 5/1/16 where they are able to use the amount given. So what i need is this:

Customer buys in and received $750 for 5/1/15 to 5/1/16
Customer uses $700 during the buyin period and has $50 left at 5/1/16
Once the next buyin year starts from 5/1/16 to 5/1/17 customer should have total avalible of $800 ($750 start each year + remainder of $50)

Yes probably way to complicated for formula but thought id ask. I have the field for whats avalible to show correct but i cant figure out how to put the date range check in. See formula field below. The remainder IF is where i need to check the date and have it role over.
IF(TEXT(Is_a_HWG_Partner__c)= "Yes" && HWG_Wine_Credit_Remainder__c = 0,0, 
IF(TEXT(Is_a_HWG_Partner__c)= "Yes" && HWG_Wine_Credit_Remainder__c <= 25, HWG_Wine_Credit_Remainder__c + HWG_Wine_Credit__c, 
IF(TEXT(Is_a_HWG_Partner__c)= "No",0, 
HWG_Wine_Credit_Remainder__c)))

 
Best Answer chosen by Ivan Winzer
William TranWilliam Tran
If I understand you right, this should do it.

Thx
 
IF(
AND (Today()>=Date(year(today()),05,01),
Today()<=Date(year(today())+1,05,01),
TEXT(Is_a_HWG_Partner__c)= "Yes") , HWG_Wine_Credit_Remainder__c + HWG_Wine_Credit__c,
IF(TEXT(Is_a_HWG_Partner__c)= "No",0,
HWG_Wine_Credit_Remainder__c))

 

All Answers

William TranWilliam Tran
What variables do you have to store the dates?

What is Is_a_HWG_Partner__c? partner for the next buying year? or current buying year?

You could just create a Renewal flag to indicate whether the user bought the next year instead of messing with dates.

Thx
Ivan WinzerIvan Winzer
Hi William,

Right now i dont have any variables to store the date as i wasnt sure if i would need them or could use the DATE() in the formula to figure it out.

The Is_a_HWG_Partner__c is a picklist field with Yes or No value and has to be yes in order for the customer to receive the credit.
And i thought about a flag but that got tricky when as each year the credit amount should refresh.

So a cusotmer bought in at 80K this year and received a credit of $750 which is good until 5/1/16. Now if they still have the flag once 5/1/16 comes they then receive a new credit amount of $750 (plus any leftovers from the pervious year) so if the remainder was $50 then for the 5/1/16 thru 5/1/17 year they would have a start amount of $800.

Hopefully that gives a little more detail. So im trying to renew their credit amount each year and add in remainders if they have any.

Ivan
William TranWilliam Tran
If I understand you right, this should do it.

Thx
 
IF(
AND (Today()>=Date(year(today()),05,01),
Today()<=Date(year(today())+1,05,01),
TEXT(Is_a_HWG_Partner__c)= "Yes") , HWG_Wine_Credit_Remainder__c + HWG_Wine_Credit__c,
IF(TEXT(Is_a_HWG_Partner__c)= "No",0,
HWG_Wine_Credit_Remainder__c))

 
This was selected as the best answer
Ivan WinzerIvan Winzer
William you are awesome that totally worked. I had to use a test date field to be able to change the date make sure it would work and it did.

Thanks,

Ivan