I am trying to create a report which shows the payments on opportunities outstanding by 1 month, 3 months and 6 months. I have created a custom field which shows the amount on each opp outstanding which will be updated by finance. The due dates will be calculated based on the start date.
Many thanks for any assistance
9 answers
Create a Custom formula field and give a label to your field Formula return type Text and the formula will be as below
IF(
OR(
ISBLANK(StartDate__c),
StartDate__c >= TODAY()
),
NULL,
IF((TODAY() - StartDate__c) < 180, "Due Over 180 Days",
IF((TODAY() - StartDate__c) < 90, "Due Over 90 Days",
IF((TODAY() - StartDate__c) < 30, "Due Over 30 Days","Due Under 30 Days"
)
)
)
)
or other alternative is just create a formula field with a return type number that returns the number of days the Opportunity is Due by and then use bucket fields on the report to group your report by different buckets and the formula will be
TODAY() - StartDate__c
Please use the insert field button to select the field API Name of StartDate field.
I think you may need to put an additional condition in the formula so that formula stops calculating the Days due if the payment has come in. Do you have a field that indicates if the payment has been made?
