I am using this formula to calculate the no of business days between two date
(Leave_Finish_Date__c - Leave_Start_Date__c) - ( FLOOR ( ( ( (Leave_Finish_Date__c) - Leave_Start_Date__c) / 7 ) ) * 2 ) + CASE(MOD ( Leave_Start_Date__c - DATE( 1900,1,6 ),7), 0,CASE( MOD ((Leave_Finish_Date__c) - DATE( 1900,1,6 ),7),0,1,2 ), 1,CASE( MOD ((Leave_Finish_Date__c) - DATE( 1900,1,6 ),7),0,2,1 ), IF(MOD(Leave_Start_Date__c - DATE( 1900,1,6 ),7) - MOD((Leave_Finish_Date__c) - DATE( 1900,1,6 ),7) <= 0 ,0, IF(MOD((Leave_Finish_Date__c) - DATE( 1900,1,6 ),7) = 0,1,-2 ) ) )))
it works fine if the finish date is not a Sat - when it is it adds on an extra day - any ideas why or how to fix it
Olivia
답변 4개
Assuming your requirement is to dedcut 1 day from Business Days if both First and Last Day Half Day boxes are checked and 0.5 day from Business Days, please try the below
(
(5 * ( FLOOR( ( Leave_Finish_Date__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( Leave_Finish_Date__c - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( Leave_Start_Date__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( Leave_Start_Date__c - DATE( 1900, 1, 8), 7 ) ) )
)
-
IF(
AND(First_Day_Half_Day__c = True, Last_Day_Half_Day__c = True),
1,
IF(
OR(First_Day_Half_Day__c = True, Last_Day_Half_Day__c = True),
0.5,0)
)
