ShowAll Questionssorted byDate Posted
Mayuri 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.

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)

ANUTEJ (Salesforce Developers)
Hi Mayuri,

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 Nehul
Hi AnuTej,
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)).