Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mayuri NehulMayuri Nehul 

how to calculate number of days excluding weekends

Hi All,
I am new to Salesforce. I have given a task to build a Leave Management System application.
I am working on a number of days excluding the weekend formula field.
As per my requirement, I have used two date fields from_date and to_date for calculating the number of days leave taken by Employee.
I am using the below formulae but not getting the correct result for the same.
for e.g, if Employee applied for leave on 12/11/20  to 19/11/20 then it should give 6 as output but getting 5.

Can anyone please help me to resolve this issue.

CASE(MOD(  (From_Date__c)  - DATE(2020,01,01),7), 
  0 , CASE( MOD( to_Date__c  - From_Date__c,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( to_Date__c - From_Date__c,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( to_Date__c- From_Date__c,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( to_Date__c - From_Date__c,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( to_Date__c - From_Date__c,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( to_Date__c - From_Date__c,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( to_Date__c - From_Date__c,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  999) 
  + 
  (FLOOR(((to_Date__c - From_Date__c)/7)*5)

Thanks in advance
ANUTEJANUTEJ (Salesforce Developers) 
Hi Mayuri,

>> https://www.cloudmybiz.com/tip-of-the-week-calculate-business-days-between-two-dates-in-salesforce/

>> https://trailblazers.salesforce.com/answers?id=9063A000000lBUUQA2

>> https://salesforcescool.blogspot.com/2018/11/calculate-business-days-in-salesforce.html

The above links have a similar implementation that you can try checking two of them have formula fields and one of them is via code.

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.
Mayuri NehulMayuri Nehul
Hi AnuTej,
Thanks for your reply.
My below formula is working but the issue is when I am selecting more than 20 leaves it is giving incorrect output
for e.g, if I have taken leave from 2/11/2020 to 2/12/2020 it should give 23 days excluding weekends but I am getting output as 24.

CASE(MOD( (From_Date__c) - DATE(1900,01,01),7),
0 , CASE( MOD( to_Date__c - From_Date__c,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( to_Date__c - From_Date__c,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( to_Date__c- From_Date__c,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( to_Date__c - From_Date__c,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( to_Date__c - From_Date__c,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( to_Date__c - From_Date__c,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( to_Date__c - From_Date__c,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(((to_Date__c - From_Date__c)/7)*5)).

Can you please help me to resolve this.

Thanks in advance