You need to sign in to do that

Don't have an account?

shoba 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.

JethaHere 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 be5.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

JethaHi 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 shobaHi Jetha

Its working good but i can't understand the logic.

JethaHere 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 be5.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

shoba shobaHi 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.

JethaIn 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.