ShowAll Questionssorted byDate Posted
Stavros 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 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 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 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 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