1

Throughout the year we have been using the following formula field from Salesforce Advanced Sample Formula to calculate the number of business days: This is recommended on the Salesforce Website Sample Date Formulas

(5 * ( FLOOR( ( date_1 - DATE( 1900, 1, 8) ) / 7 ) ) 
+ MIN( 5, MOD( date_1 - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( date_2 - DATE( 1900, 1, 8) ) / 7 ) ) 
+ MIN( 5, MOD( date_2 - DATE( 1900, 1, 8), 7 ) ) )

This field has been serving us well throughout the year, but recently as we approached the new year, we have been seeing some unusual behavior. We have rectified the issue on our end, but seems like Salesforce Documentation needs to be updated. Am I incorrect in this or are we doing something wrong?

1/6 = Date_1 1/10 = Date_2

Using Salesforce's recommended formula it calculates 4 business days, not 5.

We had to rework the formula this way to get it to calculate the correct number of days, which was using a Sunday as a reference for Date_2, rather than a Monday:

5 * (FLOOR( ( Last_Available_Date_for_Pick_Up__c - DATE( 1900, 1, 7 ) ) / 7 ) ) +
MIN(5,MOD( Last_Available_Date_for_Pick_Up__c - DATE( 1900, 1, 7 ), 7 ) )
)
-
(
5 * (FLOOR( ( Date_Available_to_Ship__c - DATE( 1900, 1, 8 ) ) / 7 )) +
MIN(5,MOD( Date_Available_to_Ship__c - DATE( 1900, 1, 8 ), 7 ) )
) 

Here is a screen shot:

Fields using the two different formulas

1 Answer 1

1

The discrepancy between the resulting values is due to how Salesforce stores Date/Time in the database vs how it is used and presented to users. In short the actual value in the data field is in UTC (GMT) time.

If you were to add the Date/Time of Oct 31 2022 5pm Mountain Daylight Time (users current time zone) the resulting value stored would be Nov 1st 2022 midnight. When viewed on a Salesforce record however the value would appear as Oct 31 5pm. This is due to Salesforce converting all times entered in reference to the users defined time zone to UTC/GMT for actual storage in the database.

Best practice to avoid confusion is to either convert all Date/time values to just Date and add a standard time such as 07/24 (7 am aka start of business day) when calculating and converting back to Date time. Or standardize all input values when calculating knowing your current time zone and the Salesforce storage values such as MDT = GMT-6 so subtract 6 hours via formula from the value in the field and calculate as needed (such as # of business’s acts between values)

What is displayed in Salesforce and what the true value is in the system differ so whatever practice you develop it’s important to keep that in mind. 1 hour can mean the difference of an entire day.

https://help.salesforce.com/s/articleView?id=000335706&type=1

You must log in to answer this question.

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