Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
Sourav P

# Calculate the number of days between two dates while excluding weekends or weekdays.

Hi,

In the below link, SF has provided the formula, althouth i am familiar with the CASE, MOD functions, i am not able to understand why its been divided by 7, and rest details within formula. also at last why to multiply by 5 .
Also, Initially, why its minus the ref date from start date ? and if the divided value is 0,1,2 etc why again its dividing by 7, and if its 1, value taken 2, if its 2, values taken 3 etc ?
If anybody can able to make me understnd the formula why its so ? Thanks
https://help.salesforce.com/articleView?id=000004526&type=1

Weekday Count Formula:

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)