+ Start a Discussion
Ryan PriestRyan Priest 

Invoice Due Date Calculations Formula

Hi SalesForce developers, looking for an easy way to get a formula field to display a due date based on 2 other fields.

Field 1 - called "cd_payment_terms__c" (formula field) | shows payment options which are pulled from a field on a seperate object and presents as text, the following options can be returned.

"In Advance"
"07 Days DOI"
"15 Days DOI"
"30 Days DOI"
"30 Days EOM"
"45 Days EOM"
"60 Days EOM"

Field 2 - called "invoice_date_c" (date field) | manually entered date that shows when an invoice is sent to a customer.

Field 3 - called "CD_Invoice_Due_Date__c" (formula field) | this field should calulate when the invoice is due based on the invoice date + a value from the "cd_payment_terms__c"

I was using the formula:

CD_Invoice_Date__c +
CASE(CD_Payment_Terms__c,
"In Advance", 0,
"07 Days DOI", 07,
"15 Days DOI", 15,
"30 Days DOI", 30,
"30 Days EOM", 30,
"45 Days EOM", 45,
"60 Days EOM", 60,
0)

Which worked for the DOI (date of invoice) which just grabbed the invoice date + the DOI value (eg. 30 Days EOI + Invoice Date), problem is the EOM (End of Month) values are harder to work out as they need to calculate - example for 30 Days EOM (Invoice Date + "last day of the invoice date month" + 30 days" etc. for 30, 45, 60.)

If no payment terms are available it should just show the invoice date, if the term "In Advance" is shown then it should be the invoice date. 

Can anyone help me out there with the formula needed for the "CD_Invoice_Due_Date__c" field?

thanks SalesForce legends!

Hargobind_SinghHargobind_Singh
You can create a new formula field to store Last-Day-Of-Invoice month and then use that value in your formula:

(Date(IF(MONTH(invoice_date__c)<12,YEAR(invoice_date__c),YEAR(invoice_date__c)+1), IF(MONTH(invoice_date__c)<12,MONTH(invoice_date__c)+1,1), 1))-1
This will give you last day of Invoice_Date__c month. 
Gaurav NirwalGaurav Nirwal
You can download a pdf file on this link which can solves your problem 

https://www.google.co.in/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&sqi=2&ved=0CBwQFjAA&url=http%3A%2F%2Fcrmsuccess.blogs.com%2Ffiles2%2F100_sample_formulas_v6.pdf&ei=Am0NVP2XJ8nkuQSszoKQBg&usg=AFQjCNFqBwjNv5idxYZY-P8ODOJOPqST4A&bvm=bv.74649129,d.c2E (https://www.google.co.in/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&sqi=2&ved=0CBwQFjAA&url=http%3A%2F%2Fcrmsuccess.blogs.com%2Ffiles2%2F100_sample_formulas_v6.pdf&ei=Am0NVP2XJ8nkuQSszoKQBg&usg=AFQjCNFqBwjNv5idxYZY-P8ODOJOPqST4A&bvm=bv.74649129,d.c2E)
Gaurav NirwalGaurav Nirwal
Use this formula

IF(
  ISBLANK( Completed_Date__c ),
  NULL,
  Completed_Date__c - ActivityDate
)
Suraj Tripathi 47Suraj Tripathi 47
Hi,

IF(AND( ISPICKVAL(Pay_Stage__c, "Invoice" ),Pay_Due_Date__c < TODAY() ), TEXT(TODAY() - Pay_Due_Date__c) & " Days for Due Date", IF( AND( ISPICKVAL(Pay_Stage__c, "Invoice" ),Pay_Due_Date__c> TODAY() ), TEXT(ABS(TODAY() - Pay_Due_Date__c)) & " Days Overdue", IF( AND( ISPICKVAL(Pay_Stage__c, "Invoice" ),Pay_Due_Date__c = TODAY() ), "Today is Duedate","" )))

You should learn more about Formula from this link:
http://resources.docs.salesforce.com/232/18/en-us/sfdc/pdf/salesforce_useful_formula_fields.pdf

If you find your Solution then mark this as the best answer.

Thank you!
 

Regards,
Suraj Tripathi