Business days between two dates off by 1

Question

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

Answer 1

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){
        addValue = 1;
    }else{
        addValue = 2;
    }
}else if(math.mod(ref.daysBetween(eShip),7) == 1){
    if(math.mod(ref.daysBetween(aShip),7) == 0){
        addValue = 2;
    }else{
        addValue = 1;
    }
}else{
    if(math.mod(ref.daysBetween(eShip),7) - math.mod(ref.daysBetween(aShip),7) <= 0){
        addValue = 0;
    }else if(math.mod(ref.daysBetween(aShip),7) == 0){
        addValue = 1;
    }else{
        addValue = -2;
    }
}

system.debug(addValue);

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){
        addValue = 1;
    }else{
        addValue = 2;
    }
}else if(math.mod(eShip.daysBetween(ref),7) == 1){
    if(math.mod(aShip.daysBetween(ref),7) == 0){
        addValue = 2;
    }else{
        addValue = 1;
    }
}else{
    if(math.mod(eShip.daysBetween(ref),7) - math.mod(aShip.daysBetween(ref),7) <= 0){
        addValue = 0;
    }else if(math.mod(aShip.daysBetween(ref),7) == 0){
        addValue = 1;
    }else{
        addValue = -2;
    }
}

system.debug(addValue);

22:27:04.20 (21294871)|USER_DEBUG|[12]|DEBUG|FLOOR: 0.0

22:27:04.20 (21447312)|USER_DEBUG|[16]|DEBUG|eShip and Ref: -1

22:27:04.20 (21553746)|USER_DEBUG|[17]|DEBUG|aShip and Ref: -2

22:27:04.20 (21936292)|USER_DEBUG|[41]|DEBUG|-2

This latter scenerio plays out just like you are saying is happening in the comments. So maybe it is just a matter of switching the order of subtraction.