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.

Improve this question

1 Answer 1

Reset to default
2

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 showcase imperfect solutions using a formula field that hardcodes/assumes one timezone, working hours, but often don't control for holidays; and often are not suitable 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. The logic could call BusinessHours.diff

Improve this answer
6
  • 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
    Commented Jan 7, 2021 at 20:29
  • 1
    and don't forget about holidays
    – cropredy
    Commented Jan 7, 2021 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
    Commented Jan 7, 2021 at 23:23
  • Updated article link: help.salesforce.com/s/articleView?id=000324878
    – Paul N
    Commented Dec 14, 2021 at 20:55
  • @Paul N the link appears to be broken.
    – Sachin Hooda
    Commented Dec 15, 2021 at 4:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .