function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Stavros McGillicuddyStavros McGillicuddy 

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

 
parth jollyparth jolly
Hello Stavros,
The formula is not executing fully is giving error somewhere else the true value would be 1 or false would be -2. Therefore its failing in 3 or 4 line of the formula .Please check the conditions that you have inserted .
Thanks
Stavros McGillicuddyStavros McGillicuddy
I cleaned up the parentheses a bit but still not behaving 
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
        )
    )
)

 
parth jollyparth jolly
I found this code online can you please try using this . 
MAX ( 0, 
( Date2 - Date1+ CASE ( MOD (Date2- DATE( 1900,1,6 ),7),0,1,1,1,0 ) ) - 
( ( FLOOR ( ( ( Date2- Date1 ) / 7 ) ) * 2 ) + 
CASE ( 
MOD ( Date1 - DATE( 1900,1,6 ),7), 
0,CASE( MOD ( Date2 - DATE( 1900,1,6 ),7),0,1,2 ), 
1,CASE( MOD ( Date2 - DATE( 1900,1,6 ),7),0,2,1 ), 
IF ( MOD ( Date1 - DATE( 1900,1,6 ),7) - MOD ( Date2 - DATE( 1900,1,6 ),7) <= 0 ,0, 
IF ( MOD ( Date2 - DATE( 1900,1,6 ),7) = 0,1,2 ) ) ) ) )
Stavros McGillicuddyStavros McGillicuddy
My code resulted in 2 where this code resulted in 0 when the Expected_Ship_Date__c is a Sunday. It should have been 1  
Date2= Actual_Ship_Date__c
Date1= Expected_Ship_Date__c