Print this page

How can I calculate business days with a formula field?

Knowledge Article Number 000003920
Description

How can a formula be created that calculates business days (essentially excluding weekends) between two date fields?
Also how to account for hours compensation, i.e. if the start date time is Monday 12:00 pm, then the end date will have to be after Tuesday 12:00 pm for one business day to show up as the value of this formula?

Resolution

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.





promote demote