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
Tracey EdlerTracey Edler 

No. of days calculation excluding weekends

Hello: The posted solution does not seem to be calculating correctly for me.

Below is my field formula to try to get the # of days overdue less the weekends. As you can see by the results, I have 3 issues
(1) only calculating 1 day for the weekend (ex: ex: Due Date = 9/19/18 and Date Sent = 9/27; one wekend involved and should be 6 days overdue but showing 7 days)
(2) adding an additional day when no weekend is involved (ex: Due Date = 9/19/18 and Date Sent = 9/21; this is no weekend involved and should be 2 days overdue but showing 3)
(3) miscaluclating when not overdue (Ex: Due Date = 9/18/18 and Date Sent = 9/17/18); no weekend involved and should be -1

Calculation ResultsUser-added image

Any advice would be greatly appreciated.

Thanks,

Tracey
 

Best Answer chosen by Tracey Edler
Alain CabonAlain Cabon
Finding the Number of Business Days Between Two Dates 
(5 * ( FLOOR( ( date1__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date1__c - DATE( 1900, 1, 8), 7 ) ) ) 
- 
(5 * ( FLOOR( ( date2__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date2__c - DATE( 1900, 1, 8), 7 ) ) )

In this formula, date_1 is the more recent date and date_2 is the earlier date. 

https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm

User-added image
 

All Answers

Raj VakatiRaj Vakati
try this 
  
((5 * ( FLOOR( (  Date_Due_Out__c- DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( Date_Due_Out__c - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( Date_Due_Out__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( Date_Due_Out__c - DATE( 1900, 1, 8), 7 ) ) ) + 1 
-
IF(AND(Date_Started__c <= DATEVALUE("2016-11-24"),Date_Completed__c >= DATEVALUE("2016-11-24")),1,0) -
IF(AND(Date_Started__c <= DATEVALUE("2016-12-23"),Date_Completed__c >= DATEVALUE("2016-12-23")),1,0) -
IF(AND(Date_Started__c <= DATEVALUE("2016-12-26"),Date_Completed__c >= DATEVALUE("2016-12-26")),1,0) -
IF(AND(Date_Started__c <= DATEVALUE("2017-01-23"),Date_Completed__c >= DATEVALUE("2017-01-01")),1,0)

 
Tracey EdlerTracey Edler
Thanks, but unfortunately all results were 1 so it did not work.
Alain CabonAlain Cabon
Finding the Number of Business Days Between Two Dates 
(5 * ( FLOOR( ( date1__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date1__c - DATE( 1900, 1, 8), 7 ) ) ) 
- 
(5 * ( FLOOR( ( date2__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date2__c - DATE( 1900, 1, 8), 7 ) ) )

In this formula, date_1 is the more recent date and date_2 is the earlier date. 

https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm

User-added image
 
This was selected as the best answer
Tracey EdlerTracey Edler
Thanks Alain, this worked for me!