Ads

Thursday 22 August 2013

How to calculate date for weekday and weekend only In Salesforce?

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 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 use 'just' formula 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)

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

No comments:

Post a Comment