+ Start a Discussion

how to write a formula to exclude weekends and public holidays for the case target completion date ?

Hello All

  Can someone help me in calculating the number of days for the target date to be populated excluding weekends and public holidays

For Instance, if the prority is in the case object is high, then the target date should be populated with +2 days from the case created date but should exclude weekends and public holidays.

I wrote the formula for excluding weekend but not able to figure how to add holidays to it. Here is the formula i wrote how to exclude weekends and add +2 days for created date

CASE( MOD( DATEVALUE( [Case].LastModifiedDate ) - DATE( 1900, 1, 7 ), 7 ), 
0, [Case].LastModifiedDate + 0 + 1,  
4, [Case].LastModifiedDate +  0+ 1, 
5, [Case].LastModifiedDate + 2 + 1,  
6, [Case].LastModifiedDate + 1 + 1, 
[Case].LastModifiedDate + 1 

Thanks in Advance
Raj VakatiRaj Vakati
To meet you are requirementes , i prefer to define the Holidays in salesforce and use the trigger to caliculate the case target completion  .. 
Bcause holidays may differ from company to company 
Thank you
I was having the same requirement in one of my projects and I met it using a trigger on Before Insert and Before Update. You can use the following code

You can use the Holiday__c object of salesforce and define holiday's over there and can calculate the days easily by removing the weekends and Holidays.

Hope, this post helps you to solve your issue.