0

I have a formula field (FirstCallDif__c) that shows in minutes the difference between two fields:

(FirstCall__c - CreatedDate) *24*60

This gives us the minutes however some leads do come in at say 3am and as such I wanted to adjust the formula if possible to show minutes from business hours.

Business Hours are 08:00-17:00 Monday-Friday -- Is it possible to update the formula so when a lead comes in at say 03:00 on a Monday and First call field gets a date/time of Monday at 08:05 that the difference field shows 5 minutes? and if comes in at 03:00 on a Sunday even it still shows 05 minutes since business hours start at 8am.

Thanks for any assistance or help with the formula.

1

Consider using Apex instead of formula.

Here's why as of January 2021 (SFDC API Version 50.0) . (@ for edits):

Be skeptical of using formula fields for datetime difference calculations because of a limitation with formula fields, quoted below:

Important: Be aware of timezone conversion issues when using formula fields to calculate datetime differences:

Within the Salesforce application, dates & date/times are stored and managed in UTC (Universal Time Zone or Greenwich Mean Time). Date and Date/Time field values are converted and displayed in the timezone specified in your user personal information settings. However, in a formula, NO TIMEZONE CONVERSION IS PERFORMED. This means, unless you happen to be in London, your date calculations will be off by the number of hours you are shifted away from GMT (and don't forget about daylight savings time). Source: https://help.salesforce.com/articleView?id=000324878

An (imperfect) formula and why its bad:

The problem with formula is, as of January 2021 they have the limitations mentioned above. This next example is imperfect like the one above is imperfect: "Find the Number of Business Hours Between Two Date/Times" here https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm

As of January 2021 it seems that declarative Formulas (incl. flows/workflows/process builder, etc ) are limited in that they cannot access setup menu's concept of BusinessHours (ie $Setup.DefaultBusinessHours doesnt exist and will not compile/save into a formula).

A system admin could solve using Apex:

Many google results settle for a imperfect solutions using long-written formulas that hardcode/assume one timezone and working hours, but they often dont control for holidays and are not a perfect solution for all user time zones. Formulas have strict length limits too so you cannot easily write formulas that handle for all those realities. Therefore until the GA Roadmap matures its declarative featureset, Apex is more accurate/durable at the moment for this question.

As always, test your apex before publishing it, and follow best practices You dont want unhandled exceptions/errors, or anything to break. To any apex newcomers, be sure to follow good "Error Handling" practices, and "Testing" and follow all the developer guides before attempting apex solutions to avoid scenarios where something fails, now or later, that potentially break your users or processes, or their dependencies. Test before you publish, and consider shipping apex with an on/off etc and recovery plans.

Keep in mind whether the timing of the calculation is flexible within your requirement; you could gain benefits (tradeoffs, really) by using async apex vs/opposed to sync apex. For example a consideration for using synchronous apex is the fact that any exceptions/errors in sync apex are potentially seen on-screen by the user, more often id say than async apex failures are seen. If the calculation can afford to be delayed, it should be, to reduce likelihood/probability that a user sees an error, unless of course its essential to show them any error immediately (ie mission critical to their operation). There is no one right approach, but do study the apex source docs of the specific version # first and foremost. One of many async apex patterns would be in a future call called appropriately from your trigger framework or that of a managed package. The main logic could perhaps call BusinessHours.diff

3
  • This is probably the benefit of been in just the one time zone since we are not experiencing that issue :) -- Is it possible to limit by business hours or would it have to be done via Apex? I have experience in Apex however I would still consider myself between beginner and Intermediate. – Craig Phoenix Jan 7 at 20:29
  • 1
    and don't forget about holidays – cropredy Jan 7 at 20:37
  • Not worried about holidays - But would love to solve the Business Hours piece, I have a Business Hours set in the Company Information section – Craig Phoenix Jan 7 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.