You need to sign in to do that
Don't have an account?
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)
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
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.
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)
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)
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