Pages

Tuesday, April 23, 2013

Salesforce: How to calculate age for weekday or weekend ?


In Salesforce, it is easy to calculate number of day between 2 date. Just create a formula field, example: End_Date__c - Start_Date__c. DONE!!!

But, is it possible to find out number of days - only weekday and only weekend - between 2 date?
Hmmm.... most of us will think about Apex Trigger. Yes, it is correct solution, apex trigger able to calculate without issue, but, if you are not from developer, you need a developer for this.

Wait a minute.... Can we 'just' use a formula field to calculate weekday and weekend?

YES, it is possible with complex formula. Here we go:

To calculate Weekday
CASE(MOD( Request_Date__c - DATE(2007,1,1),7),
0 , CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( Execution_Date__c - Request_Date__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+ (FLOOR(( Execution_Date__c - Request_Date__c )/7)*5)
-1

To calculate Weekend
CASE(MOD( Request_Date__c - DATE(2007,1,1),7),
0 , CASE( MOD( Execution_Date__c - Request_Date__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1 , CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2 , CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,0,3,1,2),
3 , CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,0,2,1,2),
4 , CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,0,1,1,2),
5 , CASE( MOD( Execution_Date__c - Request_Date__c, 7),0,1,2),
6 , CASE( MOD( Execution_Date__c - Request_Date__c, 7),6,2,1),
999)
+ (FLOOR(( Execution_Date__c - Request_Date__c )/7)*2)

Example:
Request Date = 1 Jan 2014
Execution Date = 10 Jan 2014
Total day in weekday = 7
Total day in weekend = 2

Request Date = 1 Mar 2014
Execution Date = 10 Jan 2014
Total day in weekday = 5
Total day in weekend = 4

If you see in formula above, we have DATE(2007,1,1), this is refer to 1-Jan-2007 is Monday. So, you can use any date which is Monday, example 1-Jan-1900

The rest, it is just MOD() function, have fun...


Another request is to calculate a date after X business days (business day meaning weekday from Mon - Fri). Here is the formula, the result will be a formula in Date format, while Business_Days__c is a number.

CASE( 
MOD(TODAY()- DATE(1900, 1, 7), 7), 
0, (TODAY()) + Business_Days__c+ FLOOR((Business_Days__c-1)/5)*2, 
1, (TODAY()) + Business_Days__c+ FLOOR((Business_Days__c)/5)*2, 
2, (TODAY()) + Business_Days__c+ FLOOR((Business_Days__c+1)/5)*2, 
3, (TODAY()) + Business_Days__c+ FLOOR((Business_Days__c+2)/5)*2, 
4, (TODAY()) + Business_Days__c+ FLOOR((Business_Days__c+3)/5)*2, 
5, (TODAY()) + Business_Days__c+ CEILING((Business_Days__c)/5)*2, 
6, (TODAY()) - IF(Business_Days__c > 0,1,0) + Business_Days__c + CEILING((Business_Days__c)/5)*2, 
NULL)

Result sample: today is 3 Oct 2016 and Business Days is 5, then the result would be 10 Oct 2016.