function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
shoba shobashoba shoba 

Formula field- Weekend Days Calculation

For an opportunity i want to calculate the weekends. In Sucess Community i saw  a code. But i can't understand what the logic they used.

CASE(MOD( StartDate__c - DATE(1985,6,24),7),
  0 , CASE( MOD( EndDate__c - StartDate__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
  1 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
  2 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,1,2),
  3 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,1,2),
  4 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,1,2),
  5 , CASE( MOD( EndDate__c - StartDate__c, 7),0,1,2),
  6 , CASE( MOD( EndDate__c - StartDate__c, 7),6,2,1),
  999)
  +
  (FLOOR(( EndDate__c - StartDate__c )/7)*2)
Can anyone please help to understand this.  Thanks In Advance.

 

Best Answer chosen by shoba shoba
JethaJetha
Here we have selected June 24, 1985 is a long distant Monday used as a reference point.

So our first case is used to define on which day our start date is falling, means on Monday, Tuesday...ets.

if  MOD( StartDate__c - DATE(1985,6,24) ,7), return 0 then our start date is on Monday, if it is one then Tuesday and so on.

Let's say if I choose start day as today then MOD( StartDate__c - DATE(1985,6,24) ,7) would return 2 that means it's wednesday.

corresponding choice for output 2 is  [2 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,1,2)]
 
Here I assuming that my end Date is on May 9, 2016. so if I subtract End Date from Start date , then I would get result as 12.

so my MOD( EndDate__c - StartDate__c, 7) output would be 5.  As I know my Start date is on Wednesday, so if remainder is anything more than three would have either 1 or 2 weekend days. Here I am getting 5 as outout, then I am pretty sure that I will have 2 weekends in the fraction part of my week.

(FLOOR(( EndDate__c - StartDate__c )/7)*2)  this expression is the main part used for calculating total absolute week and then multiplied by two to get total number of weekends.

Above lengthy part is only used to calculate the weekends in the fraction part of main expression.

Hope you will now have better nderstanding.

Thanks,
Jetha
 

All Answers

JethaJetha
Hi Shoba,

Please go through the article availble on salesforce community. https://help.salesforce.com/HTViewSolution?id=000004526

Please let me know if you are facing difficulties anywhere in understanding.

Thanks,
Jetha
shoba shobashoba shoba
Hi Jetha

Its working  good but i can't understand the logic.
JethaJetha
Here we have selected June 24, 1985 is a long distant Monday used as a reference point.

So our first case is used to define on which day our start date is falling, means on Monday, Tuesday...ets.

if  MOD( StartDate__c - DATE(1985,6,24) ,7), return 0 then our start date is on Monday, if it is one then Tuesday and so on.

Let's say if I choose start day as today then MOD( StartDate__c - DATE(1985,6,24) ,7) would return 2 that means it's wednesday.

corresponding choice for output 2 is  [2 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,1,2)]
 
Here I assuming that my end Date is on May 9, 2016. so if I subtract End Date from Start date , then I would get result as 12.

so my MOD( EndDate__c - StartDate__c, 7) output would be 5.  As I know my Start date is on Wednesday, so if remainder is anything more than three would have either 1 or 2 weekend days. Here I am getting 5 as outout, then I am pretty sure that I will have 2 weekends in the fraction part of my week.

(FLOOR(( EndDate__c - StartDate__c )/7)*2)  this expression is the main part used for calculating total absolute week and then multiplied by two to get total number of weekends.

Above lengthy part is only used to calculate the weekends in the fraction part of main expression.

Hope you will now have better nderstanding.

Thanks,
Jetha
 
This was selected as the best answer
shoba shobashoba shoba

Hi Jetha SF

(FLOOR(( EndDate__c - StartDate__c )/7)*2) --> This is used to calculate the absolute number of weekend.

[2 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,1,2)] ---> This i can understand but for what purpose we are using.
JethaJetha
In case if get any remainder days from (FLOOR(( EndDate__c - StartDate__c )/7)*2) this expression, so want to know how many weekends days are there in those remainder days. So adding both the expression.