+ Start a Discussion
CRMGeneralistCRMGeneralist 

Please, Please, Please Help Date Duration Field

So I am NEW at Advanced Formulas and found this formula that calculates weekend days which is great however, can I build onto this formula - What I am trying to accomplish
 
I have two fields Duration Propsect = Date it became a Prosect and Duration Opportunity = Date it became an Opportunity so then I created a field to say how many days passed, however that field gave me weekend days as well my original result was 5. Is there anyway I can use the formula below which returns just the weekend days then have it minus my two days above. I want to avoid creating another field to minus the weekend days.... Sorry if I am a complete idiot. :smileywink:
 
Prospect: 09/11/08
Opportunity: 09/15/2008
 
Duration: Takes the formula below which results as 2, but then takes the two dates about and calculates the duration so technically it would be 3 Days
 
CASE( MOD(  Duration_Prospect__c - DATE( 1985, 6, 24 ) , 7) , 0
, CASE( MOD(  Duration_Opportunity__c  - Duration_Prospect__c , 7 ),1,0,2,0,3,0,4,0,5,1,6,2,0 ), 1
, CASE( MOD( Duration_Opportunity__c- Duration_Prospect__c , 7 ),0,0,1,0,2,0,3,0,4,0,5,2,2 ), 2
, CASE( MOD( Duration_Opportunity__c- Duration_Prospect__c , 7 ),0,0,1,0,2,0,3,1,2), 3
, CASE( MOD( Duration_Opportunity__c- Duration_Prospect__c , 7 ),0,0,1,0,2,1,2), 4
, CASE( MOD( Duration_Opportunity__c- Duration_Prospect__c , 7 ),0,0,1,1,2), 5
, CASE( MOD( Duration_Opportunity__c- Duration_Prospect__c , 7 ),0,1,2), 6
, CASE( MOD( Duration_Opportunity__c - Duration_Prospect__c , 7 ),6,2,1)
, 666 )
+
( FLOOR( ( Duration_Opportunity__c - Duration_Prospect__c ) / 7 ) * 2 )
CRMGeneralistCRMGeneralist
Well I was able to accomplish my goal but as I feared I had to create all these fields and formulas there was no easy way. However, my boss thinks it's rockin and now it is done.