0

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. – Adrian Larson Sep 11 '16 at 22:38
  • 1
    What do you mean "return Sunday result is 2"? – Eric Sep 11 '16 at 22:38
  • I tried to remove most of your unnecessary parentheses. They make the formula much harder to read. – Adrian Larson Sep 11 '16 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] – Stavros_McGillicuddy Sep 11 '16 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 '16 at 23:52
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.