1

I pulled a formula from the Success boards to calculate the total minutes between 2 date/time fields, but exclude weekend hours. However, it is spitting out a value of "0" when the value should clearly be "1". Do you know what is off about the below formula?

IF( 
ISBLANK(End_Date__c ), 
(CASE(MOD( DATEVALUE(Start_Date__c) - DATE(1985,6,24),7), 
0 , CASE( MOD( TODAY() - DATEVALUE(Start_Date__c),7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( TODAY() - DATEVALUE(Start_Date__c),7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( TODAY() - DATEVALUE(Start_Date__c),7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( TODAY() - DATEVALUE(Start_Date__c),7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( TODAY() - DATEVALUE(Start_Date__c),7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( TODAY() - DATEVALUE(Start_Date__c),7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( TODAY() - DATEVALUE(Start_Date__c),7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR(( TODAY() - DATEVALUE(Start_Date__c))/7)*5))-1+NOW() - Start_Date__c-(TODAY() - DATEVALUE(Start_Date__c)), 

(CASE(MOD( DATEVALUE(Start_Date__c) - DATE(1985,6,24),7), 
0 , CASE( MOD( DATEVALUE( End_Date__c ) - DATEVALUE(Start_Date__c),7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( DATEVALUE( End_Date__c ) - DATEVALUE(Start_Date__c),7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( DATEVALUE( End_Date__c ) - DATEVALUE(Start_Date__c),7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( DATEVALUE( End_Date__c ) - DATEVALUE(Start_Date__c),7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( DATEVALUE( End_Date__c ) - DATEVALUE(Start_Date__c),7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( DATEVALUE( End_Date__c ) - DATEVALUE(Start_Date__c),7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( DATEVALUE( End_Date__c ) - DATEVALUE(Start_Date__c),7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR(( DATEVALUE( End_Date__c ) - DATEVALUE(Start_Date__c))/7)*5) 
)-1+(End_Date__c - Start_Date__c-(DATEVALUE( End_Date__c ) - DATEVALUE(Start_Date__c))) 

)
  • The value clearly should be 1? What are your inputs? Please specify any input/output combos that you believe to be incorrect. – Adrian Larson Jun 15 '17 at 18:44
  • I just tried it on another record, and it's still spitting out "0" instead of the expected outcome on this record, 5. The inputs were: 6/15/2017 2:51 PM and 6/15/2017 2:56 PM. – Avi Fuld Jun 15 '17 at 18:58
  • @AvijitChakraborty, I tried that one there, and received the same incorrect value as the formula above. Any other ideas? – Avi Fuld Jun 16 '17 at 14:34

Your Answer

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

Browse other questions tagged or ask your own question.