# Business days between two dates off by 1

Why does this formula return Sunday result is 2 when it should be 1

``````Actual_Ship_Date__c - Expected_Ship_Date__c -
FLOOR(
(Actual_Ship_Date__c - Expected_Ship_Date__c) / 7
) * 2 +
CASE(MOD(Expected_Ship_Date__c - DATE(1900,1,6), 7),
0, CASE(MOD(Actual_Ship_Date__c - DATE(1900,1,6), 7),
0, 1, 2
),
1, CASE(MOD(Actual_Ship_Date__c - DATE(1900,1,6), 7),
0, 2, 1
),
IF(
MOD(Expected_Ship_Date__c - DATE(1900,1,6), 7) -
MOD(Actual_Ship_Date__c - DATE(1900,1,6), 7) <= 0,
0, IF(MOD(Actual_Ship_Date__c - DATE(1900,1,6), 7) = 0,
1, -2
)
)
)
``````
• That is a truly excessive use of parentheses. Sep 11, 2016 at 22:38
• What do you mean "return Sunday result is 2"?
– Eric
Sep 11, 2016 at 22:38
• I tried to remove most of your unnecessary parentheses. They make the formula much harder to read. Sep 11, 2016 at 22:48
• @AdrianLarson thanks for cleaning it up.. So my issue is If the Expected Ship Date is Sept 11, 2016 (Sunday) and the Actual Ship Date is Sept 12, 2016 (Monday) the formula returns 2 rather than 1 [day] Sep 11, 2016 at 23:15
• @Stavros - is it just that scenario that fails or does friday and saturday return 2 as well? what about fri-sun does it return 3 or 2.
– Eric
Sep 11, 2016 at 23:52

So I put this in some code for you to run through. I have not had time to analyze it completely and will do so maybe tomorrow. hopefully it will help you find the issue:

``````date eShip = date.newinstance(2016,09,11);
date aShip = date.newInstance(2016,09,12);
date ref = date.newinstance(1900,1,6);

integer aShipMeShip = eShip.daysBetween(aShip);

Decimal floorResult = math.FLOOR(
(aShipMeShip) / 7) * 2;

System.debug('FLOOR: ' + floorResult);

Integer addValue = 0;

system.debug('eShip and Ref: ' + math.mod(ref.daysBetween(eShip),7));
system.debug('aShip and Ref: ' + math.mod(ref.daysBetween(aShip),7));

if(math.mod(ref.daysBetween(eShip),7) == 0){
if(math.mod(ref.daysBetween(aShip),7) == 0){
}else{
}
}else if(math.mod(ref.daysBetween(eShip),7) == 1){
if(math.mod(ref.daysBetween(aShip),7) == 0){
}else{
}
}else{
if(math.mod(ref.daysBetween(eShip),7) - math.mod(ref.daysBetween(aShip),7) <= 0){
}else if(math.mod(ref.daysBetween(aShip),7) == 0){
}else{
}
}

``````

The Output keep in mind that the debugs are as follows:

1. the Result of the first floor
2. The value of MOD Expected Shipping - Ref
3. The value of MOD Actual Shipping - Ref

22:20:48.24 (26382353)|USER_DEBUG|[11]|DEBUG|FLOOR: 0.0

22:20:48.24 (26570522)|USER_DEBUG|[15]|DEBUG|eShip and Ref: 1

22:20:48.24 (26722565)|USER_DEBUG|[16]|DEBUG|aShip and Ref: 2

22:20:48.24 (27020389)|USER_DEBUG|[40]|DEBUG|1

Which seems to work correctly.

however when I reverse the days between it provides a negative value and I get the -2 which in your formal would ADD 2 and give the result you are seeing

``````date eShip = date.newinstance(2016,09,11);
date aShip = date.newInstance(2016,09,12);
date ref = date.newinstance(1900,1,6);

integer aShipMeShip = eShip.daysBetween(aShip);

Decimal floorResult = math.FLOOR(
(aShipMeShip) / 7) * 2;

System.debug('FLOOR: ' + floorResult);

Integer addValue = 0;

system.debug('eShip and Ref: ' + math.mod(eShip.daysBetween(ref),7));
system.debug('aShip and Ref: ' + math.mod(aShip.daysBetween(ref),7));

if(math.mod(eShip.daysBetween(ref),7) == 0){
if(math.mod(aShip.daysBetween(ref),7) == 0){
}else{
}
}else if(math.mod(eShip.daysBetween(ref),7) == 1){
if(math.mod(aShip.daysBetween(ref),7) == 0){
}else{
}
}else{
if(math.mod(eShip.daysBetween(ref),7) - math.mod(aShip.daysBetween(ref),7) <= 0){
}else if(math.mod(aShip.daysBetween(ref),7) == 0){
}else{
}
}