Introduction Ideally this logic should be implemented in an Apex trigger, but if there are no available developer to create Apex code, the below mentioned formula can come in handy. Notice that this must be thoroughly tested to check if it meets business requirements.
Notice the formula is provided "as is" and is not supported by Salesforce.com due to its extreme complexity.
Implementation considerations
This declarative approach requires a formula field and two field updates. Notice actual bank holidays cannot be taken into consideration.
a) Business Day Formula Field. We assume the start date time field is Start_Datetime__c and the end date time field is End_Datetime__c:
IF(DATEVALUE(Start_Datetime__c) <> TODAY(),
/* If TRUE */ IF( /* Is same business week? */ AND( (CASE(MOD(End_Datetime_GMT_date__c  DATE(1985, 1, 5), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0) + CASE(MOD(Start_Datetime_GMT_date__c  DATE(1985, 1, 5), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0) >5), End_Datetime_GMT_date__c  Start_Datetime_GMT_date__c < 6 ), /* Then work out the number of business days with this formula */ CASE(MOD(End_Datetime_GMT_date__c  DATE(1985, 1, 5), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0) + CASE(MOD(Start_Datetime_GMT_date__c  DATE(1985, 1, 5), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)  6, /* Otherwise use this formula */ FLOOR( (End_Datetime_GMT_date__c (Start_Datetime_GMT_date__c + CASE(MOD(Start_Datetime_GMT_date__c  DATE(1985, 1, 5), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) )/7)*5 +CASE(MOD(Start_Datetime_GMT_date__c  DATE(1985, 1, 5), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0) +CASE(MOD(End_Datetime_GMT_date__c  DATE(1985, 1, 5), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0) ),
/* If FALSE */ 0)
+
/* Compensate depending on start and end time */ IF( /* End datetime's time of the day in minutes referred to 0:00 GMT */ VALUE(LEFT(RIGHT(TEXT(End_Datetime__c),9),2))*60 + VALUE(LEFT(RIGHT(TEXT(End_Datetime__c),6),2)) /* Start datetime's time of the day in minutes referred to 0:00 GMT */ < VALUE(LEFT(RIGHT(TEXT(Start_Datetime__c),9),2))*60+VALUE(LEFT(RIGHT(TEXT(Start_Datetime__c),6),2)) , 1, 0)
b) Field updates required:
b.1) Start_Datetime_GMT_date__c requires field update with formula DATEVALUE(LEFT(TEXT(Start_Datetime__c),10))
b.2) End_Datetime_GMT_date__c requires field update with formula DATEVALUE(LEFT(TEXT(End_Datetime__c),10))
How does this formula work?
1) DATE(1985, 1, 5) was Saturday.
2) DATE(1985, 1, 5) + n*7 days, where n=1,2,3, ..., will always be a Saturday
3) Let's define A_Date__c as "Previous Saturday" + m, where m represents the number of days from A_Date__c to the previous Saturday to A_Date__c and takes values 0, 1, 2, 3, 4, 5, 6. For instance, if A_Date__c is 18th July 2011 we could put it like A_Date__c = 16th July 2011 + 2 (m = 2).
4) If we substract DATE(1985, 1, 5) from A_Date__c, that's equivalent to
x = A_Date__C  DATE(1985, 1, 5) = "Previous Saturday" + m  DATE(1985, 1, 5) = ("Previous Saturday"  DATE(1985, 1, 5)) + m
where ("Previous Saturday"  DATE(1985, 1, 5)) is going to be always n times 7, where n = 0, 1, 2, ...
I.e. x = m + n*7.
5) By definition, MOD(x, 7) = MOD(m+7*n, 7) = MOD(m,7) + MOD(7*n,7) = MOD(n, 7) = n. Therefore MOD(A_Date__c  DATE(1985,1,5)) is actually the number of whole days from A_Date__c to the previous Saturday to A_Date__c.
6) So
CASE(MOD(A_Date__c  DATE(1985, 1, 5), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)
is whole business days from A_Date__c (including A_Date__c) to the previous Saturday to A_Date__c. I.e. if A_Date__c is a Monday this will be 1; if A_Date__c is a Friday, this will be 5.
And
CASE(MOD(A_Date__c  DATE(1985, 1, 5), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)
is whole business days from A_Date__c (including A_Date__c) to the next Saturday to A_Date__c. If A_Date__c is a Monday, that will be 5 whole days. If A_Date is a Friday that will be 1 day.
