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
shobana Ganesanshobana Ganesan 

Formula Field to exclude weekends and business holiday.

Hi Experts,

I have a bussiness need to exclude weekends and bussiness holiday for the report purpose, kindly help me out on this.
Requirement:
"Formual_Field__c = (Difference in days between the Current_Date__c and Custom_Status_Date__c excluding the weekend and holidays)"

Thanks in Advance,
Shobana.G
 
Khan AnasKhan Anas (Salesforce Developers) 
Hi Shobana,

I trust you are doing very well.

It is an idea (https://success.salesforce.com/ideaView?id=08730000000I75dAAC) to exclude both holidays and weekends in Formula.

If you want to calculate the number of days between two dates while excluding weekends or weekdays, you can refer to below knowledge article.

https://help.salesforce.com/articleView?id=000004526&type=1

However, you can use this Apex code to exclude holidays and weekends in Formula.

https://sfdcdev.wordpress.com/2011/09/24/handling-holidays-in-salesforce-apex/

Also, please refer to below link which might help you further.

https://success.salesforce.com/answers?id=9063A0000019QHUQA2


I hope it helps you.

Kindly let me know if it helps you and close your query by marking it as solved so that it can help others in the future.

Thanks and Regards,
Khan Anas
SathishPanjalaSathishPanjala

Hi Shobana, 

In order to achieve this requirement you must go with Business hours and Holiday table in Salesforce SetUp >> Business Hours  [ Place your company business hours here ] / SetUp >> Holiday [ Place your company holidays here ] 

It'll automatically exclude the weekends.

By querying both of these tables in a Trigger /Class you get the accurate results rather a formula field.

Please Mark this as solved if it works.
 

Sathish P
SathishPanjalaSathishPanjala
If you still want to go with FORMULA field ..... Below is the formula to calculate the number of days between two dates while excluding weekends.

However it'll not exculde the HOLIDAYS, Becuase each org has its own holidays based on region /country. Hense salesforce dont have knowledge about list of holidays.
 
CASE(MOD( StartDate__c - DATE(1985,6,24),7), 
  0 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( EndDate__c - StartDate__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(( EndDate__c - StartDate__c )/7)*5) 


Steps to create:
Create a formula field that returns a number.
Paste in one of the two formulas.
Replace StartDate__c and EndDate__c with your custom field values.
If using Date/Time fields: Replace with DATEVALUE(YourCustomDateTime__c) instead.

Notes:

Weekdays are defined as Mon-Fri and weekends as Sat & Sun
Holidays are not addressed
June 24, 1985 is a long distant Monday used as a reference point
The result will include BOTH the START and END dates.
Mon-Sun is counted as 5 Weekdays and 2 Weekend Days.
Mon-Fri is NOT (Fri subtract Mon) = 4 Elapsed Days.
Sat-Sun is NOT (Sun subtract Sat) = 1 Elapsed Day.
If you use another Formula field as the start or end dates you MAY hit a compilation limit.
Workaround - Use workflow rules to save the output of the formula fields into a regular date field.

Reference: Knowledge Article (https://help.salesforce.com/articleView?id=000004526&type=1)

Please mark it as solved if it answer your question.

Sathish P
 
shobana Ganesanshobana Ganesan
Thank u so much Sathis P 
prudhviraj pallam 22prudhviraj pallam 22
Hi,

I am using the same code but I am not able to execute that. I was getting an error message as "Error: Syntax error. Missing ')'". I am trying with almost same code. But I am not able to do that can you please help me in that.
Munawir IslamiMunawir Islami
hey guys, please refer to this link if you still need this function https://medium.com/@munawirrahman/count-duration-exclude-weekends-and-public-holidays-in-salesforce-without-any-codes-285277df35d
Benjamin Hogan 4Benjamin Hogan 4
Hello guys, just wanted to post some results of what I got out of all the answers.

If you want to achieve deducting business hours using a formula field, or using a formula in a workflow to update a field, use the formula provided below. This formula give the number of business hours rounded to the nearest hour, so you lose minute precision. However you can probably adjust the formula to give minutes as well. 
ROUND( 8 * (
( 5 * FLOOR( ( DATEVALUE( dateTime__1 ) - DATE( 1900, 1, 8) ) /
7) +
MIN(5,
MOD( DATEVALUE( date/time_1 ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 8 * ( MOD( date/time_1 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( date/time_2 ) - DATE( 1900, 1, 8) ) /
7) +
MIN( 5,
MOD( DATEVALUE( date/time_2 ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 8 * ( MOD( date/time_2 - DATETIMEVALUE( '1900-01-08 16:00:00' ), 1) ) )
)
)
),
0 )
Src: https://resources.docs.salesforce.com/218/latest/en-us/sfdc/pdf/salesforce_useful_formula_fields.pdf

If you are comfortable writing an Apex class to return the business hours, you can very easily accomplish this. Just go to setup and search Business Hours. Enter your business hours and even holidays. Now, create an Apex class that calls the diff() method of the BusinessHours class to get the difference between two times while deducting your business hours. That easy.

The signature of the diff(Id, DateTime, DateTime) method is:
diff(businessHoursId, startDate, endDate)
where startDate and endDate are DateTime fields and the difference in milliseconds is returned in a Long.

Example of method that calculates business minutes between two DateTimes using the BusinessHours class and diff(Id, DateTIme, DateTime) method: 
public static Integer GetElapsedBusinessMinutes(Datetime startTime, Datetime endTime) {
		BusinessHours bh = [SELECT Id FROM BusinessHours WHERE IsDefault=true];
        if(startTime == null || endTime == null || startTime == endTime) {
            return 0;
        } else {
            Long elapsedMilliseconds = BusinessHours.diff(bh.Id, startTime, endTime);
            Long elapsedSeconds = elapsedMilliseconds / 1000;
            Long elapsedMinutes =  elapsedSeconds / 60;
        // Don't return negative values.
            if(elapsedMinutes < 0){
                return 0;
            } else {
                return elapsedMinutes.intValue();
            }
        }
    }
Business Hours class: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_classes_businesshours.htm#apex_System_BusinessHours_diff
 
Adam Paine 18Adam Paine 18
@Munawir - the activity date on my holiday records reflect the date at the time the holiday was created, not the most recent recurrence.  Is there a way you've gotten that to work?  Thanks.
Himanshu Tiwari 19Himanshu Tiwari 19
I want to calculate the end date if number of days is given and start date is given while excluding weekends and holidays.
Norhasnifalina Razali 10Norhasnifalina Razali 10
Hi I am using this formula to exclude weekend. However, it return error as per attached picture. Is there any way to check where was the extrac comma coming from?


​​​​​​
CASE(MOD( DATEVALUE(Assigned_to_CS__c)- DATE(1985,6,24),7),
  0 , CASE( MOD(DATEVALUE(Approval_Request_Sent__c) - DATEVALUE(Assigned_to_CS__c ,7)),1,2,2,3,3,4,4,5,5,5,6,5,1),
  1 , CASE( MOD(DATEVALUE(Approval_Request_Sent__c) - DATEVALUE(Assigned_to_CS__c ,7)),1,2,2,3,3,4,4,4,5,4,6,5,1),
  2 , CASE( MOD(DATEVALUE(Approval_Request_Sent__c) - DATEVALUE(Assigned_to_CS__c ,7))),1,2,2,3,3,3,4,3,5,4,6,5,1),
  3 , CASE( MOD(DATEVALUE(Approval_Request_Sent__c) - DATEVALUE(Assigned_to_CS__c ,7)),1,2,2,2,3,2,4,3,5,4,6,5,1),
  4 , CASE( MOD(DATEVALUE(Approval_Request_Sent__c) - DATEVALUE(Assigned_to_CS__c ,7)),1,1,2,1,3,2,4,3,5,4,6,5,1),
  5 , CASE( MOD(DATEVALUE(Approval_Request_Sent__c) - DATEVALUE(Assigned_to_CS__c ,7)),1,0,2,1,3,2,4,3,5,4,6,5,0),
  6 , CASE( MOD(DATEVALUE(Approval_Request_Sent__c) - DATEVALUE(Assigned_to_CS__c ,7)),1,1,2,2,3,3,4,4,5,5,6,5,0),
  999)
  +
  (FLOOR ((( DATEVALUE(Approval_Request_Sent__c) -DATEVALUE(Assigned_to_CS__c ))/7)*5)*24)


User-added image