function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
GRStevenBrookesGRStevenBrookes 

Woking Days

Hi,

I'm looking for a formula that's adds business days only, I.e Friday + 3 business days = Tuesday

Any help appreciated.
Josephadm401Josephadm401

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.

shruthishruthi

You can use this formula below to add 3 business days

CASE(MOD(TODAY()- DATE(1900, 1, 7), 7), 0, TODAY()+3, 1, TODAY()+3, 2, TODAY()+3,3, TODAY()+5, 4, TODAY()+5, 5, TODAY()+5, 6, TODAY()+4,null)



Check out the thread here for more answers!