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
Kirill_YunussovKirill_Yunussov 

Formula calculating the time between two dates in business days, FYI only

Hi,

 

Had to spend time and think about this formula for reporting, so here you are, in case you ever need it.  It does not consider national holidays, and the calculations are based purely on days of the week.

 

Comments:

First, it takes the number of weeks betwen the two dates, and multiplies it by 5 (number of business days per week).

Then, it adds the remainder of the time period divided by 7.

Then, it subtracts 2 from the result (for Saturday and Sunday) if the time period included an extra weekend in it.

 

 

5*FLOOR((Delivery_Date__c-Ship_Date__c)/7)+MOD(Delivery_Date__c-Ship_Date__c,7)-
IF(
  CASE(MOD(Ship_Date__c-DATE(1900,1,7),7),
    1,1,
    2,2,
    3,3,
    4,4,
    5,5,
    6,6,
    0,7,
    0)
  <
  CASE(MOD(Delivery_Date__c-DATE(1900,1,7),7),
    1,1,
    2,2,
    3,3,
    4,4,
    5,5,
    6,6,
    0,7,
    0),
  0,2)

Best Answer chosen by Admin (Salesforce Developers) 
Kirill_YunussovKirill_Yunussov

Revised formula, to consider that products may ship or deliver on Saturday or Sunday. 

 

 

 

5*FLOOR((Delivery_Date__c-Ship_Date__c)/7)


+MOD(
   CASE(MOD(Delivery_Date__c-DATE(1900,1,7),7),
     1,Delivery_Date__c,
     2,Delivery_Date__c,
     3,Delivery_Date__c,
     4,Delivery_Date__c,
     5,Delivery_Date__c,
     6,Delivery_Date__c-1,
     0,Delivery_Date__c-2,
     Delivery_Date__c)
  -CASE(MOD(Ship_Date__c-DATE(1900,1,7),7),
     1,Ship_Date__c,
     2,Ship_Date__c,
     3,Ship_Date__c,
     4,Ship_Date__c,
     5,Ship_Date__c,
     6,Ship_Date__c-1,
     0,Ship_Date__c-2,
     Ship_Date__c),
  7)

-IF(
  CASE(MOD(Ship_Date__c-DATE(1900,1,7),7),
    1,1,
    2,2,
    3,3,
    4,4,
    5,5,
    6,6,
    0,7,
    0)
  <
  CASE(MOD(Delivery_Date__c-DATE(1900,1,7),7),
    1,1,
    2,2,
    3,3,
    4,4,
    5,5,
    6,6,
    0,7,
    0),
  0,2)

All Answers

Kirill_YunussovKirill_Yunussov

Also, this formula does not consider that things may be shipped or delivered on a non-business day.   I ll need to upgrade it a little to consider that.
Kirill_YunussovKirill_Yunussov

Revised formula, to consider that products may ship or deliver on Saturday or Sunday. 

 

 

 

5*FLOOR((Delivery_Date__c-Ship_Date__c)/7)


+MOD(
   CASE(MOD(Delivery_Date__c-DATE(1900,1,7),7),
     1,Delivery_Date__c,
     2,Delivery_Date__c,
     3,Delivery_Date__c,
     4,Delivery_Date__c,
     5,Delivery_Date__c,
     6,Delivery_Date__c-1,
     0,Delivery_Date__c-2,
     Delivery_Date__c)
  -CASE(MOD(Ship_Date__c-DATE(1900,1,7),7),
     1,Ship_Date__c,
     2,Ship_Date__c,
     3,Ship_Date__c,
     4,Ship_Date__c,
     5,Ship_Date__c,
     6,Ship_Date__c-1,
     0,Ship_Date__c-2,
     Ship_Date__c),
  7)

-IF(
  CASE(MOD(Ship_Date__c-DATE(1900,1,7),7),
    1,1,
    2,2,
    3,3,
    4,4,
    5,5,
    6,6,
    0,7,
    0)
  <
  CASE(MOD(Delivery_Date__c-DATE(1900,1,7),7),
    1,1,
    2,2,
    3,3,
    4,4,
    5,5,
    6,6,
    0,7,
    0),
  0,2)

This was selected as the best answer
Kirill_YunussovKirill_Yunussov

A corrected formula, now works without bugs.

 

5*FLOOR((
  CASE(MOD(Actual_Delivery_Date__c-DATE(1900,1,7),7),
     1,Actual_Delivery_Date__c,
     2,Actual_Delivery_Date__c,
     3,Actual_Delivery_Date__c,
     4,Actual_Delivery_Date__c,
     5,Actual_Delivery_Date__c,
     6,Actual_Delivery_Date__c-1,
     0,Actual_Delivery_Date__c-2,
     Actual_Delivery_Date__c)
  -CASE(MOD(IBM_Actual_Ship_Date__c-DATE(1900,1,7),7),
     1,IBM_Actual_Ship_Date__c,
     2,IBM_Actual_Ship_Date__c,
     3,IBM_Actual_Ship_Date__c,
     4,IBM_Actual_Ship_Date__c,
     5,IBM_Actual_Ship_Date__c,
     6,IBM_Actual_Ship_Date__c-1,
     0,IBM_Actual_Ship_Date__c-2,
     IBM_Actual_Ship_Date__c))/7)

+MOD(
   CASE(MOD(Actual_Delivery_Date__c-DATE(1900,1,7),7),
     1,Actual_Delivery_Date__c,
     2,Actual_Delivery_Date__c,
     3,Actual_Delivery_Date__c,
     4,Actual_Delivery_Date__c,
     5,Actual_Delivery_Date__c,
     6,Actual_Delivery_Date__c-1,
     0,Actual_Delivery_Date__c-2,
     Actual_Delivery_Date__c)
  -CASE(MOD(IBM_Actual_Ship_Date__c-DATE(1900,1,7),7),
     1,IBM_Actual_Ship_Date__c,
     2,IBM_Actual_Ship_Date__c,
     3,IBM_Actual_Ship_Date__c,
     4,IBM_Actual_Ship_Date__c,
     5,IBM_Actual_Ship_Date__c,
     6,IBM_Actual_Ship_Date__c-1,
     0,IBM_Actual_Ship_Date__c-2,
     IBM_Actual_Ship_Date__c),
  7)

-IF(
  CASE(MOD(IBM_Actual_Ship_Date__c-DATE(1900,1,7),7),
    1,1,
    2,2,
    3,3,
    4,4,
    5,5,
    6,5,
    0,5,
    0)
   <=
  CASE(MOD(Actual_Delivery_Date__c-DATE(1900,1,7),7),
    1,1,
    2,2,
    3,3,
    4,4,
    5,5,
    6,5,
    0,5,
    0),
  0,2)
SadhuSadhu

Hi Kirill,

 

The formula is good, but I'm looking for a formula which returns the value in hours and minutes for business hours between 06:00 to 24:00. Any help on this will be greatful.

 

Thanks,

Jairaj