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.
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
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
Its working good but i can't understand the logic.
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
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.