function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Timesync0Timesync0 

Formula to calculate payment amount based on date

I have the need to create a formula field that returns an amount based on the following logic:

1.  There is an initial one-time payment amount.  That amount (#1) is annotated manually in a field
2.  That payment will be made on the Friday following record creation.
3.  Once that payment is made, there is a seperate payment that will be made bi-weekly. That amount (#2) is also annotated in a field
4.  I need to have the calculated field populated with the appropriate amount.  If it is before the Friday followiing the account creation, then the amount will be #1.  If the date is past the first Friday post account creattion, then the amount will be #2.

I do not think I can use the "IF" function as I believe that only returns a non-numeric. Is this something for the "CASE" function?

Help??
Jeff TalbotJeff Talbot
The formula I've suggested below is one potential way to get what you need. You may need to tweak this some depending on your needs.
 
One scenario you didn't address is if the current day is ON the Friday followiing the account creation. In the formula below, I've adressed this and assumed that each Friday you will pay on records entered through Thursday, and that any records entered on Friday will be paid the following Friday.
 
To be more specific, the formula below will produce $0 on the day the record is created. It won't pull a value from your amount1 or amount2 fields until the day AFTER the record is created. I did this so you don't pay a record on the same day it was entered. Otherwise you may end up with a record entered Friday at 8am that you payed, and another entered Friday at 4pm that you haven't paid, and no way to tell the difference. The formula below should (if I did this right - please test yourself before implementing) take records entered on a Friday and show $0 that Friday, and amount1 the following Friday, and amount 2 after that.
 
IF(TODAY()-DATEVALUE(CreatedDate)=0,0,
IF(
TODAY()-
(CASE(MOD(DATEVALUE(CreatedDate) - DATE(1900, 1, 7), 7),
0,DATEVALUE(CreatedDate)+6,
1,DATEVALUE(CreatedDate)+5,
2,DATEVALUE(CreatedDate)+4,
3,DATEVALUE(CreatedDate)+3,
4,DATEVALUE(CreatedDate)+2,
5,DATEVALUE(CreatedDate)+1,
6,DATEVALUE(CreatedDate)+7,
DATE(1900,12,31)))
<0, Amount1__c , Amount2__c ))