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

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)﻿

Kirill_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)﻿

Kirill_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_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_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)﻿