Skip to content

Dave Helgerson

Salesforce Software Development and Consulting

Menu
  • Contact
  • Resume
Menu

Salesforce Date Formula for Finding the Next Business Day that Skips Holidays

February 3, 2018February 3, 2018

Finding the next business day using a Salesforce formula field can be challenging, and making the formula skip holiday dates increases the complexity. Below is what I’ve come up with after digging through many examples on the web, and borrowing logic that made sense. I would not call this great because it requires updating the formula field each year, and it is likely that this will be forgotten. But, here it is.

CASE(
 CASE(
  MOD( DATEVALUE(My_Date_Field__c) - DATE(1900, 1, 7), 7),
  0, DATEVALUE(My_Date_Field__c)+1, /*if Sun,CloseDate+1=Monday */
  1, DATEVALUE(My_Date_Field__c)+1, /*if Mon,CloseDate+1=Tuesday */
  2, DATEVALUE(My_Date_Field__c)+1, /*if Tues,CloseDate+1=Wednesday */
  3, DATEVALUE(My_Date_Field__c)+1, /*if Wed,CloseDate+1=Thursday*/
  4, DATEVALUE(My_Date_Field__c)+1, /*if Thurs,CloseDate+1=Friday */
  5,DATEVALUE(My_Date_Field__c)+3, /*if Fri,CloseDate+3=Monday next week */
  6,DATEVALUE(My_Date_Field__c)+2, /*if Sat,CloseDate+2=Monday next week */
  null
 ) 
, DATEVALUE("2018-01-01"),DATEVALUE("2018-01-02") /* New Year's Day */
, DATEVALUE("2018-01-15"),DATEVALUE("2018-01-16") /* Martin Luther King Jr.'s Birthday */
, DATEVALUE("2018-02-19"),DATEVALUE("2018-01-20") /* Washington's Birthday */
, DATEVALUE("2018-05-28"),DATEVALUE("2018-01-29") /* Memorial Day */ 
, DATEVALUE("2018-07-04"),DATEVALUE("2018-07-05") /* Independence Day */
, DATEVALUE("2018-09-03"),DATEVALUE("2018-09-04") /* Labor Day */
, DATEVALUE("2018-10-08"),DATEVALUE("2018-10-09") /* Columbus Day */
, DATEVALUE("2018-11-22"),DATEVALUE("2018-11-23") /* Thanksgiving Day */
, DATEVALUE("2018-12-25"),DATEVALUE("2018-12-26") /* Christmas Day */
, DATEVALUE("2019-01-01"),DATEVALUE("2019-01-02") /* New Year's Day */
, CASE(
  MOD( DATEVALUE(My_Date_Field__c) - DATE(1900, 1, 7), 7),
  0, DATEVALUE(My_Date_Field__c)+1, /*if Sun,CloseDate+1=Monday */
  1, DATEVALUE(My_Date_Field__c)+1, /*if Mon,CloseDate+1=Tuesday */
  2, DATEVALUE(My_Date_Field__c)+1, /*if Tues,CloseDate+1=Wednesday */
  3, DATEVALUE(My_Date_Field__c)+1, /*if Wed,CloseDate+1=Thursday*/
  4, DATEVALUE(My_Date_Field__c)+1, /*if Thurs,CloseDate+1=Friday */
  5,DATEVALUE(My_Date_Field__c)+3, /*if Fri,CloseDate+3=Monday next week */
  6,DATEVALUE(My_Date_Field__c)+2, /*if Sat,CloseDate+2=Monday next week */
  null
 )
)

Related

Super Clone Pro
© 2023 Dave Helgerson | Powered by Minimalist Blog WordPress Theme