Ask Search:
Sourav PSourav P 

How to remove the week-ends and holidays from a Task aging field

Hi,
I have a task againg field , formula as below ( type " number")
LastModifiedDate - CreatedDate
But i want to exclude the sat-sun and a public holiday list from this number. May i know how to modify the formula ? thnx
 
Best Answer chosen by Sourav P
Arjun AnilkumarArjun Anilkumar
Hi Sourav, 
Hope this would work 

CASE(MOD( CreatedDate - DATE(1985,6,24),7), 
  0 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( LastModifiedDate  - CreatedDate )/7)*5) - IF('Holiday Date' > CreatedDate && 'Holiday Date < endDate,1,0 )

Replace 'Holiday Date' with the public Holiday dates, if there are more than one public holidays, the for each date there will be a 'IF' condition similar to the one at the end of the above formula

All Answers

Arjun AnilkumarArjun Anilkumar
Hi Sourav, 
Hope this would work 

CASE(MOD( CreatedDate - DATE(1985,6,24),7), 
  0 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( LastModifiedDate  - CreatedDate )/7)*5) - IF('Holiday Date' > CreatedDate && 'Holiday Date < endDate,1,0 )

Replace 'Holiday Date' with the public Holiday dates, if there are more than one public holidays, the for each date there will be a 'IF' condition similar to the one at the end of the above formula
This was selected as the best answer
Sourav PSourav P
Hi Arjun,
Thanks. But Can you plz make me understand this a bit plz.
I understand the CASE & MOD functions. But firstly why if the reminder is 0, 1 etc we are again divinding by 7 and if the reminder is 1,then we take 2. if 2 we take 3. I couldnt understand this.
Arjun AnilkumarArjun Anilkumar
Sourav,
Regarding the Case And Mod functions and the formula used to calculate no of weekDays please refer this Link
https://help.salesforce.com/articleView?id=000004526&type=1
Regarding the If condition used
1. Consider "25/12/2018" is a Public Holiday, the Created date is "20/12/2018 and Last Modified to be "01/01/2019"
Now the Part before the If Condition Gives you the number of Working Days between them.
the if condition checks if the Holiday Daye is between the created date and the last modified date and it should be a weekday as well, and if it is then, one is subtracted.
there is a small change from the formula given in the above comment.

Hope this Helps


CASE(MOD( CreatedDate - DATE(1985,6,24),7), 
  0 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( LastModifiedDate  - CreatedDate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( LastModifiedDate  - CreatedDate )/7)*5) - IF(AND(WEEKDAY(Holiday Date),('Holiday Date' > CreatedDate && 'Holiday Date < endDate)),1,0 )
 
Sourav PSourav P
Hi Arjun,
Thanks a lot. I can understand the Holiday part. But till couldn't able to about the weekdays one, i refer the link but its not explained in details , so difficult to understand why it has divided by 7. May be i will raise another query for that.