Part4=======>>>Formulas for:::
• Date Calculations
• Discounting
• Expense Tracking
• Financial Calculations
• Integration Links
Date Calculations
Day of the Year
Calculates today’s numeric day of the year (a number between 1 and 365)
|
|
Formula----->TODAY() – DATE(YEAR(TODAY()), 1, 1) + 1
Day of Week (number)
Calculates today’s day of the week as a number (0 = Sunday, 1 = Monday, 2 = Tuesday, etc.). This formula can be applied to any date field by substituting the date field instead of the TODAY function.
|
|
Formula----->MOD(TODAY() - DATE(1900, 1, 7), 7)
Day of Week (text)
Calculates today’s day of the week and displays as text. This formula can be applied to any date field by substituting the date field instead of the TODAY function.
|
|
Formula----->CASE(MOD( TODAY() - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error")
DISCOUNTING
Opportunity Discounting
Calculates a discounted Opportunity amount using a simple percentage. This is a simplistic scenario where discount is applied at the Opportunity level, rather than the Opportunity Line level. Discount Percent is a custom field on Opportunity.
|
|
Formula----->ROUND({!Amount} – {!Amount} * {!DiscountPercent__c}, 2)
EXPENSE TRACKING
Mileage Expense Calculation
Calculates mileage expenses associated with visiting a customer site, at 35 cents per mile.
|
|
Formula------>Mileage Expense = {!Miles_Driven__c} * 0.35
Expense Number String Connect
Displays the text "Expense# " followed by the expense number. This is a text formula field that uses an expense number custom field.
|
|
Formula------>"Expense# "& {!ExpenseNumber__c}
FINANCIAL CALCULATIONS
Compound Interest
Calculates interest, compounded m times per year, you will have after t years.
|
|
Formula----->{!P} * ( 1 + {!R} / {!M} ) ^ ( {!T} * {!M) )
Continuously Compounding Interest
Calculates interest, if continuously compounded, you will have after t years.
|
|
Formula----->{!P} * EXP({!R} * {!T})
Payment Status
Determines if the payment due date is past and the payment status is "UNPAID." If so, returns the text "PAYMENT OVERDUE" and if not, leaves the field blank. This example uses a custom date field called Payment Due Date and a text custom field called Payment Status on contracts.
|
|
Formula------>IF(AND({!Payment_Due_Date__c} < TODAY(),{!Payment_Status__c} ="UNPAID") , "PAYMENT OVERDUE", null )
Payment Due Indicator
Returns the date five days after the contract start date whenever Payment Due Date is blank. Payment Due Date is a custom date field on contracts.
|
|
Formula------>NULLVALUE({!Payment_Due_Date__c} , {!StartDate} +5)
Rules-based Status Message
Uses conditional logic to display a "Payment Overdue" status message for open invoices.
|
|
Formula------>IF(AND({!PaymentDueDate__c} < TODAY(), ISPICKVAL({!PaymentStatus},"UNPAID")), "PAYMENT OVERDUE", "")
INTEGRATION LINKS
Application API Link
Creates a link to an application outside Salesforce, passing the parameters so that it can connect to Salesforce via the API and create the necessary event.
|
|
Formula------>HYPERLINK("https://www.myintegration.com?sId="& GETSESSIONID() & "?&rowID="&{!Name} & "action=CreateTask","Create a Meeting Request")
Skype Auto Dialer Integration
Creates a linkable phone number field that automatically dials the phone number via the Skype VOIP phone application. This example requires installation of the Skype application (a third-party product not provided by salesforce.com) on your desktop and modification of your PC’s registry to associate the "callto:" protocol with the Skype application.
|
|
Formula------>HYPERLINK("callto://+" & {!Country_Code__c} & {!Phone_Unformatted__c}, {!Phone})
• Date Calculations
• Discounting
• Expense Tracking
• Financial Calculations
• Integration Links
Date Calculations
Day of the Year
Calculates today’s numeric day of the year (a number between 1 and 365)
|
|
Formula----->TODAY() – DATE(YEAR(TODAY()), 1, 1) + 1
Day of Week (number)
Calculates today’s day of the week as a number (0 = Sunday, 1 = Monday, 2 = Tuesday, etc.). This formula can be applied to any date field by substituting the date field instead of the TODAY function.
|
|
Formula----->MOD(TODAY() - DATE(1900, 1, 7), 7)
Day of Week (text)
Calculates today’s day of the week and displays as text. This formula can be applied to any date field by substituting the date field instead of the TODAY function.
|
|
Formula----->CASE(MOD( TODAY() - DATE(1900, 1, 7), 7), 0, "Sunday", 1, "Monday", 2, "Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6, "Saturday","Error")
DISCOUNTING
Opportunity Discounting
Calculates a discounted Opportunity amount using a simple percentage. This is a simplistic scenario where discount is applied at the Opportunity level, rather than the Opportunity Line level. Discount Percent is a custom field on Opportunity.
|
|
Formula----->ROUND({!Amount} – {!Amount} * {!DiscountPercent__c}, 2)
EXPENSE TRACKING
Mileage Expense Calculation
Calculates mileage expenses associated with visiting a customer site, at 35 cents per mile.
|
|
Formula------>Mileage Expense = {!Miles_Driven__c} * 0.35
Expense Number String Connect
Displays the text "Expense# " followed by the expense number. This is a text formula field that uses an expense number custom field.
|
|
Formula------>"Expense# "& {!ExpenseNumber__c}
FINANCIAL CALCULATIONS
Compound Interest
Calculates interest, compounded m times per year, you will have after t years.
|
|
Formula----->{!P} * ( 1 + {!R} / {!M} ) ^ ( {!T} * {!M) )
Continuously Compounding Interest
Calculates interest, if continuously compounded, you will have after t years.
|
|
Formula----->{!P} * EXP({!R} * {!T})
Payment Status
Determines if the payment due date is past and the payment status is "UNPAID." If so, returns the text "PAYMENT OVERDUE" and if not, leaves the field blank. This example uses a custom date field called Payment Due Date and a text custom field called Payment Status on contracts.
|
|
Formula------>IF(AND({!Payment_Due_Date__c} < TODAY(),{!Payment_Status__c} ="UNPAID") , "PAYMENT OVERDUE", null )
Payment Due Indicator
Returns the date five days after the contract start date whenever Payment Due Date is blank. Payment Due Date is a custom date field on contracts.
|
|
Formula------>NULLVALUE({!Payment_Due_Date__c} , {!StartDate} +5)
Rules-based Status Message
Uses conditional logic to display a "Payment Overdue" status message for open invoices.
|
|
Formula------>IF(AND({!PaymentDueDate__c} < TODAY(), ISPICKVAL({!PaymentStatus},"UNPAID")), "PAYMENT OVERDUE", "")
INTEGRATION LINKS
Application API Link
Creates a link to an application outside Salesforce, passing the parameters so that it can connect to Salesforce via the API and create the necessary event.
|
|
Formula------>HYPERLINK("https://www.myintegration.com?sId="& GETSESSIONID() & "?&rowID="&{!Name} & "action=CreateTask","Create a Meeting Request")
Skype Auto Dialer Integration
Creates a linkable phone number field that automatically dials the phone number via the Skype VOIP phone application. This example requires installation of the Skype application (a third-party product not provided by salesforce.com) on your desktop and modification of your PC’s registry to associate the "callto:" protocol with the Skype application.
|
|
Formula------>HYPERLINK("callto://+" & {!Country_Code__c} & {!Phone_Unformatted__c}, {!Phone})
