Spazure Silicon blogged this tip on his/her blog with the caveat that "So, even though this formula won't ever be on the CRM Best Practices Custom Formula blog, I hope somebody finds it, and finds it useful." So, mainly to prove him/her wrong, but also because it's very useful, here it is - a function to convert Date/Time into HH:MM:SS.

This particular formula was designed for cases, calculating the elapsed time in HH:MM:SS between right now and the time the case was created. As you see, I had to use the TEXT() function to convert the numerically output Date/Time function results into text, which were then fed into the Concatenate function to make everything display nicely on one field. My original version of this formula actually used multiple fields, {!Hours} {!Seconds} {!Minutes}. This made the final formula look prettier, and helped with debugging, but ultimately was impractical for distribution.

So, even though this formula won't ever be on the CRM Best Practices Custom Formula blog, I hope somebody finds it, and finds it useful. If you use my formula, please comment -- not because I want credit, but because I really do like to know where my code ends up. :-)

Text(FLOOR((NOW() - {!CreatedDate}) * 24))

&":"&Text(FLOOR(((NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24))* 60))

&":"&Text(FLOOR((( (NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24 )) * 60 - FLOOR(((NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24 )) * 60 )) * 60 ))

Spazure Silicon blogged this tip on his/her blog with the caveat that "So, even though this formula won't ever be on the CRM Best Practices Custom Formula blog, I hope somebody finds it, and finds it useful." So, mainly to prove him/her wrong, but also because it's very useful, here it is - a function to convert Date/Time into HH:MM:SS.

This particular formula was designed for cases, calculating the elapsed time in HH:MM:SS between right now and the time the case was created. As you see, I had to use the TEXT() function to convert the numerically output Date/Time function results into text, which were then fed into the Concatenate function to make everything display nicely on one field. My original version of this formula actually used multiple fields, {!Hours} {!Seconds} {!Minutes}. This made the final formula look prettier, and helped with debugging, but ultimately was impractical for distribution.

So, even though this formula won't ever be on the CRM Best Practices Custom Formula blog, I hope somebody finds it, and finds it useful. If you use my formula, please comment -- not because I want credit, but because I really do like to know where my code ends up. :-)

Text(FLOOR((NOW() - {!CreatedDate}) * 24))

&":"&Text(FLOOR(((NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24))* 60))

&":"&Text(FLOOR((( (NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24 )) * 60 - FLOOR(((NOW() - {!CreatedDate}) * 24 - FLOOR((NOW() - {!CreatedDate}) * 24 )) * 60 )) * 60 ))

Those of you experienced formula authors out there have probably groaned more than a few times when building advanced conditional logic based on a picklist field value. In the previous release of custom formulas, you had to build nested IF() functions with embedded ISPICKVAL() functions in order to accomplish this feat.

Well, there’s good news for you in the Winter ’06 release: You can now use picklist fields directly with the CASE() function. No ISPICKVAL() required.  This has a number of big advantages:

1. The formula is easier to create and read
   
2. Using CASE can result in a significantly smaller compiled size, which is important for complex formulas that are approaching the 4K max compiled size limit.

Converting your existing picklist formulas to use the CASE function is easy. Here’s an example formula based on the Opportunity Stage picklist to use as a model:

Those of you experienced formula authors out there have probably groaned more than a few times when building advanced conditional logic based on a picklist field value. In the previous release of custom formulas, you had to build nested IF() functions with embedded ISPICKVAL() functions in order to accomplish this feat.

Well, there’s good news for you in the Winter ’06 release: You can now use picklist fields directly with the CASE() function. No ISPICKVAL() required.  This has a number of big advantages:

1. The formula is easier to create and read
   
2. Using CASE can result in a significantly smaller compiled size, which is important for complex formulas that are approaching the 4K max compiled size limit.

Converting your existing picklist formulas to use the CASE function is easy. Here’s an example formula based on the Opportunity Stage picklist to use as a model:

To help people experience the power of image fields, I created a very simple AppExchange component which allows you to quickly install nine working examples ranging from priority flags, to product photos, to stock charts.

This free component takes less than two minutes to install and it’s built on a stand-alone custom tab so you can add it to your account without affecting any of your users.  One thing to note, when you install this component, you'll have to fill in the blanks (score, ticker, image url) for some of the images to appear.

The idea is that once you have these sample formulas in your account, you can reapply the them to any of your other objects. Give it a try!

To help people experience the power of image fields, I created a very simple AppExchange component which allows you to quickly install nine working examples ranging from priority flags, to product photos, to stock charts.

This free component takes less than two minutes to install and it’s built on a stand-alone custom tab so you can add it to your account without affecting any of your users.  One thing to note, when you install this component, you'll have to fill in the blanks (score, ticker, image url) for some of the images to appear.

The idea is that once you have these sample formulas in your account, you can reapply the them to any of your other objects. Give it a try!

Over the past couple of days we’ve all been digesting the exciting new features in the Winter ’06 release. So far I’d have to say my favorite has been the image fields. It’s amazing how dramatically you can change your users experience with this simple trick.

For those of you who have watched my Dreamforce Presentation you know that I use Salesforce.com in an unconventional manner but I think that this use case for image fields can be extended to all sorts of situations.

With image fields I’m now able to get a quick visual representation of what's being published out to the website. I have a field on this page for “image url” and another for “asset url.” With these two data points I was able to create a formula field to displays the thumbnail image for each piece of content and it's even clickable so you can quickly launching the underlying presentation or document.

HYPERLINK({!Asset_URL_c},
IMAGE({!Image_URL_c},"Image Not Available"))

Over the past couple of days we’ve all been digesting the exciting new features in the Winter ’06 release. So far I’d have to say my favorite has been the image fields. It’s amazing how dramatically you can change your users experience with this simple trick.

For those of you who have watched my Dreamforce Presentation you know that I use Salesforce.com in an unconventional manner but I think that this use case for image fields can be extended to all sorts of situations.

With image fields I’m now able to get a quick visual representation of what's being published out to the website. I have a field on this page for “image url” and another for “asset url.” With these two data points I was able to create a formula field to displays the thumbnail image for each piece of content and it's even clickable so you can quickly launching the underlying presentation or document.

HYPERLINK({!Asset_URL_c},
IMAGE({!Image_URL_c},"Image Not Available"))

With the Winter ’06 release, formula fields allow you to incorporate images into standard and custom tabs, list views, and reports. Because people recognize images more quickly than they can read words, adding images to Salesforce can enable users to find the data they’re looking for more quickly. For example,

• Photos for product catalogs or employee directories
• Graphical representations of status, priority, or rating
• Clickable Buttons
• Online Presence Indicator

As an example, you can add green, yellow, or red flag images to the Cases tab to provide a quick visual indication of case priority.

Formula Return Type:
Text

Formula:
IMAGE(
CASE( {!Priority},
"Low", "/img/samples/flag_green.gif",
"Medium", "/img/samples/flag_yellow.gif",
"High", "/img/samples/flag_red.gif",
"/s.gif"),
"priority flag")

For tips & techniques and many more samples, be sure to check out Getting Started with Image Fields on the Image Fields home page on successforce.com. 

With the Winter ’06 release, formula fields allow you to incorporate images into standard and custom tabs, list views, and reports. Because people recognize images more quickly than they can read words, adding images to Salesforce can enable users to find the data they’re looking for more quickly. For example,

• Photos for product catalogs or employee directories
• Graphical representations of status, priority, or rating
• Clickable Buttons
• Online Presence Indicator

As an example, you can add green, yellow, or red flag images to the Cases tab to provide a quick visual indication of case priority.

Formula Return Type:
Text

Formula:
IMAGE(
CASE( {!Priority},
"Low", "/img/samples/flag_green.gif",
"Medium", "/img/samples/flag_yellow.gif",
"High", "/img/samples/flag_red.gif",
"/s.gif"),
"priority flag")

For tips & techniques and many more samples, be sure to check out Getting Started with Image Fields on the Image Fields home page on successforce.com. 

Have you ever had a need to know the day of the week an opportunity closed? What about excluding Saturdays and Sundays from your reports so that your dashboards only show business days?

Day of week calculations are easy with formula fields, using the technique described below.

This example calculates the day of the week as a number (0 = Saturday, 1 = Sunday, 2 = Monday, 3 = Tuesday, etc.) for Opportunity Close Date.

Formula Return Type:
Number

Formula:
MOD( {!CloseDate} - DATE(1900, 1, 6), 7)

Remarks: 

• This formula works by picking a known Saturday (January 6, 1900 in the example), subtracting it from the provided date, and using MOD to divide the result by 7 and return the remainder. Thanks to Simon W. and Jesse C. for this example.
• Exclude Saturday & Sunday from reports by adding a report selection criteria of "Day of Week greater than 2".
• A text version of this formula could spell out the day of the week:

CASE(MOD( {!CloseDate} - DATE(1900, 1, 6), 7), 0, "Saturday", 1, "Sunday", 2, "Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6, "Friday","")

Have you ever had a need to know the day of the week an opportunity closed? What about excluding Saturdays and Sundays from your reports so that your dashboards only show business days?

Day of week calculations are easy with formula fields, using the technique described below.

This example calculates the day of the week as a number (0 = Saturday, 1 = Sunday, 2 = Monday, 3 = Tuesday, etc.) for Opportunity Close Date.

Formula Return Type:
Number

Formula:
MOD( {!CloseDate} - DATE(1900, 1, 6), 7)

Remarks: 

• This formula works by picking a known Saturday (January 6, 1900 in the example), subtracting it from the provided date, and using MOD to divide the result by 7 and return the remainder. Thanks to Simon W. and Jesse C. for this example.
• Exclude Saturday & Sunday from reports by adding a report selection criteria of "Day of Week greater than 2".
• A text version of this formula could spell out the day of the week:

CASE(MOD( {!CloseDate} - DATE(1900, 1, 6), 7), 0, "Saturday", 1, "Sunday", 2, "Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6, "Friday","")

There are some important checkbox fields in salesforce.com that you may find useful to reference in formulas.  "Do not call" and "Email opt out" on the Contacts tab are but a couple examples.

Is this possible, you ask?  Yes, but there's a trick to it: checkbox fields are treated as Booleans with a value of TRUE for checked and FALSE for unchecked.

You can use a checkbox field directly in an IF function as follows:

IF ({!HasOptedOutOfEmail} , "checkbox is checked", "checkbox is unchecked" )

Note that if you compare a checkbox field to the literal TRUE or FALSE in an IF statement, you will get the following syntax error.

IF ({!HasOptedOutOfEmail} = TRUE , "checkbox is checked", "checkbox is unchecked" )
Error: Incorrect argument type for function =.

There are some important checkbox fields in salesforce.com that you may find useful to reference in formulas.  "Do not call" and "Email opt out" on the Contacts tab are but a couple examples.

Is this possible, you ask?  Yes, but there's a trick to it: checkbox fields are treated as Booleans with a value of TRUE for checked and FALSE for unchecked.

You can use a checkbox field directly in an IF function as follows:

IF ({!HasOptedOutOfEmail} , "checkbox is checked", "checkbox is unchecked" )

Note that if you compare a checkbox field to the literal TRUE or FALSE in an IF statement, you will get the following syntax error.

IF ({!HasOptedOutOfEmail} = TRUE , "checkbox is checked", "checkbox is unchecked" )
Error: Incorrect argument type for function =.

Custom formulas were not intended as a solution for data validation, and we have plans to provide robust field and cross-field validation capabilities in the future.

However, if you are anxious for field validation logic today, it is possible to implement a workaround with a custom formula field, a custom report, and/or workflow.

1. Create a custom formula field with a return type of Text
2. Use an IF function to evaluate one or more validation conditions.  If an error condition is found, return a textual description of the error.  If there are no errors, return an empty string ("").
3. Create a custom report that lists data records where the custom formula field is non-blank.
4. Alternatively, you can create a workflow rule that creates a task or sends an email notification when the custom formula field is non-blank.

For example, here is an Opportunity formula that checks if a custom field Renewal Expiration Date is after the Close Date. 

IF ({!Expiration_Date__c} > {!CloseDate}, "Error: renewal expiration date cannot be after close date", "")

>The major limitation with this approach is that it does not prevent the user from saving invalid data.  Rather, it enables you to detect invalid data after it has been saved, so that it can be corrected.

Custom formulas were not intended as a solution for data validation, and we have plans to provide robust field and cross-field validation capabilities in the future.

However, if you are anxious for field validation logic today, it is possible to implement a workaround with a custom formula field, a custom report, and/or workflow.

1. Create a custom formula field with a return type of Text
2. Use an IF function to evaluate one or more validation conditions.  If an error condition is found, return a textual description of the error.  If there are no errors, return an empty string ("").
3. Create a custom report that lists data records where the custom formula field is non-blank.
4. Alternatively, you can create a workflow rule that creates a task or sends an email notification when the custom formula field is non-blank.

For example, here is an Opportunity formula that checks if a custom field Renewal Expiration Date is after the Close Date. 

IF ({!Expiration_Date__c} > {!CloseDate}, "Error: renewal expiration date cannot be after close date", "")

>The major limitation with this approach is that it does not prevent the user from saving invalid data.  Rather, it enables you to detect invalid data after it has been saved, so that it can be corrected.

"Territory management" means something different to every sales organization, and the requirements can get quite complex.

However, sometimes a few simple rules are sufficient to get the job done. For example, if you just need to categorize your accounts into regions, based on state or province.

The Region formula below returns a text value of North, South, East, West, or Central based on the Billing State of the account.

"Territory management" means something different to every sales organization, and the requirements can get quite complex.

However, sometimes a few simple rules are sufficient to get the job done. For example, if you just need to categorize your accounts into regions, based on state or province.

The Region formula below returns a text value of North, South, East, West, or Central based on the Billing State of the account.

One common application for formula fields is to track commission percentages. For example, in the formula to the left, the logic says that if the amount is less than $10,000, the commission should be set to 4%; but if it’s greater, then the accelerator should kick in, resulting in a 5% commission.

Formula:
IF({!Amount} < 100000, 0.04, 0.05)

This number can be displayed on the opportunity page layout to motivate your sales reps and it can also be used in a report formula to calculate your commission payouts at the end of the month.

It’s worth noting that when working with percent fields in formulas, 5% should be entered as 0.05. You have to lead with a zero or it will return an error.

One common application for formula fields is to track commission percentages. For example, in the formula to the left, the logic says that if the amount is less than $10,000, the commission should be set to 4%; but if it’s greater, then the accelerator should kick in, resulting in a 5% commission.

Formula:
IF({!Amount} < 100000, 0.04, 0.05)

This number can be displayed on the opportunity page layout to motivate your sales reps and it can also be used in a report formula to calculate your commission payouts at the end of the month.

It’s worth noting that when working with percent fields in formulas, 5% should be entered as 0.05. You have to lead with a zero or it will return an error.

Custom formulas are very powerful and flexible, so the sky is the limit on what you can do. For some ideas, refer to the 100 Sample Formulas To Get You Started document or review examples in the Summer '05 Release Notes.

All editions.

Custom formulas are very powerful and flexible, so the sky is the limit on what you can do. For some ideas, refer to the 100 Sample Formulas To Get You Started document or review examples in the Summer '05 Release Notes.

All editions.

No. Custom formulas are recalculated with each request, so they are guaranteed to always be up-to-date, whether viewed on tabs, list views, reports, or through the sforce API.

No. Custom formulas are recalculated with each request, so they are guaranteed to always be up-to-date, whether viewed on tabs, list views, reports, or through the sforce API.

The number of custom formulas is limited only by the number of custom fields allowed per tab in each edition. In general*, these limits are:

* Enterprise Edition (500)
* Professional Edition (100)
* Team Edition (10)

The Activity and User objects have significantly lower limits (20).

The number of custom formulas is limited only by the number of custom fields allowed per tab in each edition. In general*, these limits are:

* Enterprise Edition (500)
* Professional Edition (100)
* Team Edition (10)

The Activity and User objects have significantly lower limits (20).

There is a limit of 1000 characters per custom formula, including spaces and carriage returns. However, because you can reference other formulas within formulas, you can sometimes divide up big formulas into multiple fields to get around this limit.

There are other limits that are related to the size of the database query generated by the system. You may run into these limits if you create custom formulas that reference other custom formulas. If this happens, you will get a syntax validation error stating, “formula too large to execute.”

There is a limit of 1000 characters per custom formula, including spaces and carriage returns. However, because you can reference other formulas within formulas, you can sometimes divide up big formulas into multiple fields to get around this limit.

There are other limits that are related to the size of the database query generated by the system. You may run into these limits if you create custom formulas that reference other custom formulas. If this happens, you will get a syntax validation error stating, “formula too large to execute.”

No. In Summer ‘05, custom formulas can only reference standard and custom fields (including other formulas!) within the same object. They cannot reference fields in their “parent” object, “child” objects (i.e., related lists), or other related objects.  This means, for example, that a custom formula on Opportunity cannot reference fields on Account (parent) or Opportunity Product (child).

We plan to allow formulas to reference fields in other tabs and related lists in the future. If you want to add your voice to make this enhancement a priority, please file a feature request case for the following feature:

- Foreign field references in formulas – ability to reference fields in parent or other related objects

No. In Summer ‘05, custom formulas can only reference standard and custom fields (including other formulas!) within the same object. They cannot reference fields in their “parent” object, “child” objects (i.e., related lists), or other related objects.  This means, for example, that a custom formula on Opportunity cannot reference fields on Account (parent) or Opportunity Product (child).

We plan to allow formulas to reference fields in other tabs and related lists in the future. If you want to add your voice to make this enhancement a priority, please file a feature request case for the following feature:

- Foreign field references in formulas – ability to reference fields in parent or other related objects

No. In Summer ‘05, custom formulas can only reference standard and custom fields within the same object. They cannot reference fields in their “child” objects (i.e., related lists).   

We plan to support sums & counts in the future. If you want to add your voice to make this enhancement a priority, please file a feature request case f or the following feature:

- Summary Fields – ability to count or sum fields in children objects (i.e., related lists)

No. In Summer ‘05, custom formulas can only reference standard and custom fields within the same object. They cannot reference fields in their “child” objects (i.e., related lists).   

We plan to support sums & counts in the future. If you want to add your voice to make this enhancement a priority, please file a feature request case f or the following feature:

- Summary Fields – ability to count or sum fields in children objects (i.e., related lists)

This is the error message that indicates a run-time error occurred during formula calculation. The most common cause of this error is division by zero. This often happens if your formula divides by a non-required field, and the field is blank for some records. See the online help or release notes for more information on troubleshooting formula error conditions.

This is the error message that indicates a run-time error occurred during formula calculation. The most common cause of this error is division by zero. This often happens if your formula divides by a non-required field, and the field is blank for some records. See the online help or release notes for more information on troubleshooting formula error conditions.

Custom formulas are not indexed for searching because they are dynamically recalculated for each request.

They are not available in Offline Edition or Outlook Edition (planned for the future).
They are not included in data exports (planned for the future).
Custom formulas on Lead cannot be mapped for lead conversion.

The following field types cannot be referenced in a custom formula:
- Description
- Long Text Area
- Multi-Select Picklists

Custom formulas are not indexed for searching because they are dynamically recalculated for each request.

They are not available in Offline Edition or Outlook Edition (planned for the future).
They are not included in data exports (planned for the future).
Custom formulas on Lead cannot be mapped for lead conversion.

The following field types cannot be referenced in a custom formula:
- Description
- Long Text Area
- Multi-Select Picklists

Yes, custom formula fields can be used in workflow rules.

However, the rules for triggering workflow have not changed.  Workflow rules are only evaluated when a record is created or updated. 

This means that you cannot use a custom formula as a way to trigger a workflow rule based on the passage of time.  For example, if you create a custom formula field on Case called "Days Open", you can't create a workflow rule that automatically triggers when Days Open > 10.  Well, to be precise, you you can create the workflow rule, but it will only be evaluated when the Case is updated by an end user or through the API.

Time-based workflow is on the Customforce product roadmap.

Yes, custom formula fields can be used in workflow rules.

However, the rules for triggering workflow have not changed.  Workflow rules are only evaluated when a record is created or updated. 

This means that you cannot use a custom formula as a way to trigger a workflow rule based on the passage of time.  For example, if you create a custom formula field on Case called "Days Open", you can't create a workflow rule that automatically triggers when Days Open > 10.  Well, to be precise, you you can create the workflow rule, but it will only be evaluated when the Case is updated by an end user or through the API.

Time-based workflow is on the Customforce product roadmap.