0

I am stuck with an issue with editing Salesforce formula for counting business hours between 2 dates (provided by Salesforce here):

ROUND( 8 * (
   ( 5 * FLOOR( ( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8) ) / 7) +
    MIN(5, 
     MOD( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8), 7) +
     MIN( 1, 24 / 8 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
    ) 
   )
 -
   ( 5 * FLOOR( ( DATEVALUE( date/time_2 ) - DATE( 1900, 1, 8) ) / 7) +
     MIN( 5,
      MOD( DATEVALUE( date/time_2 ) - DATE( 1996, 1, 1), 7 ) +
      MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
    )
   ) 
  ), 
0 )

So this is not an edited formula that counts hours between 2 business dates in case if work days start at 9 AM PDT and last 8 hours. BUT! What I need - count all hours between 2 date/time fields (not an issue - just changed all "8" to "24" in the formula) but still do not count weekends. The issue is that we need to adapt this formula for use by users from different time zones and I have no clue how to change this part

DATETIMEVALUE( '1900-01-08 16:00:00' )

In the documentation we have: "If you live in a different time zone or your work day doesn’t start at 9:00 AM, change the reference time to the start of your work day in GMT.". But how to adapt it dynamically?

By the solution from here I'm able to get the current user's timezone offset and use it to add/subtract from the date/time values. But a different variant of using it didn't help me.

Except this, even for one user formula sometimes gives the wrong result if the date/time frame starts/end during the weekends.

Worth mentioning that the formula is used in flow. So maybe any ideas on how the data can be manipulated in flow as well? Apex we can use only as a last resort:)

5
  • Can't you just use this formula to calculate the number of business days and then just add the time zone difference to the result?
    – Kasper
    Jan 25 at 3:43
  • See if this Flow date/Datetime action component from UnofficialSF.com can be of use. Dealing with TZ in formulas leads to unreadable formulas
    – cropredy
    Jan 25 at 4:38
  • @Kasper good idea but the main issue here and in other calculations - is when date/time frame is around weekends. For example, when we have a user with a time zone -6 GMT, 1 date - Fri 11 PM, 2nd date - Mon 1 AM - excluding weekends difference is 2 hours. Converted to GMT it will be Sat 5 AM and Mon 7 AM. So excluding weekends the result will be 7 hours minus time zone offset 6 h - the result is wrong - 1 h. Jan 25 at 8:29
  • @cropredy thank you! I am currently trying to adapt it as well. But calculation results are still wrong is some cases. Jan 25 at 12:53
  • @IvanSholok ah, thanks, i hadn't thought of that. Sounds like quite the challenge.
    – Kasper
    Jan 26 at 7:57

1 Answer 1

0

To whom it can be interesting in the future, I gave up with the formula and ended with the Apex Invocable method solution. So I have created new Business hours in the org (the default one is different and can't be used in this case) in GMT timezone and where weekends do not have hours and all other days - 24 hours. Business hours Id is queried in the flow and passed to the Invocable together with 2 date/time fields and as a result, it returns the hours difference. Important note! Seems that Date/time fields are also transforming to GMT when the BusinessHours.diff method is used. So I have used a solution from here to add the TimeZone offset of the current user to the date/time fields which are transformed back to the user date/time during the calculation. For example when it’s 1 pm by my user’s Time Zone (+2) we add 2 more hours (by using the Offset value) and transform the time to 3 pm. Later when it is used in the calculations this time is transformed back to 1 pm.

Here is the Apex code (feel free to use and improve it:-)):

public with sharing class InvocableCalculateBusinessHours {

@InvocableMethod(label = 'Calculate Business Hours' description = 'Get business hours')
public static List<Results> execute(List<Requests> requestList) {
    Decimal hoursDiff;
    String statusMessage;

    try {
        if (requestList != null && !requestList.isEmpty()) {
            String businessHoursId = requestList[0].BusinessHoursInput;
            Datetime firstDate = requestList[0].firstDate;
            Datetime secondDate = requestList[0].secondDate;
            Decimal msDiff = BusinessHours.diff(businessHoursId, firstDate, secondDate).intValue();
            hoursDiff = msDiff.divide(3600000, 2);
            statusMessage = 'Success';
        } else {
            hoursDiff = null;
            statusMessage = 'Input values are empty';
        }
    } catch (Exception e) {
        hoursDiff = null;
        System.debug('Error: ' + e.getMessage());
        statusMessage = 'Error: ' + e.getMessage();
    }

    Results response = new Results();
    response.outputHours = hoursDiff;
    response.responseStatus = statusMessage;

    List<Results> responseWrapper = new List<Results>();
    responseWrapper.add(response);

    return responseWrapper;
}

public class Requests {

    @InvocableVariable(label = 'First Date' required = true)
    public Datetime firstDate;
    @InvocableVariable(label = 'Second Date' required = true)
    public Datetime secondDate;
    @InvocableVariable(label = 'Business Hours' required = true)
    public String BusinessHoursInput;

}

public class Results {

    @InvocableVariable(label = 'Hours difference' required = true)
    public Decimal outputHours;
    @InvocableVariable(label = 'Response status' required = true)
    public String responseStatus;

}

}

You must log in to answer this question.

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