ShowAll Questionssorted byDate Posted
Matthew Bracewell

# Working out the number of days between 2 dates without weekends including decimals for half days etc

Hi All,

We use echosign and i want to track the number of working days between the day / time the contract is signed (echosign_dev1__DateSigned__c ) and the day our team input the agreement into another legacy system (echosign_dev1__Opportunity__r.Date_Contract_Completed__c)

I found the below code referenced on many success forums and tried it:

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

I obviously replaced the relevant end and start dates / times with my dates referenced above as shown below:

CASE(MOD( echosign_dev1__DateSignedDate__c - DATE(1985,6,24),7),

0 , CASE( MOD( echosign_dev1__Opportunity__r.Date_Contract_Completed__c - echosign_dev1__DateSigned__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( echosign_dev1__Opportunity__r.Date_Contract_Completed__c - echosign_dev1__DateSigned__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( echosign_dev1__Opportunity__r.Date_Contract_Completed__c - echosign_dev1__DateSigned__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( echosign_dev1__Opportunity__r.Date_Contract_Completed__c - echosign_dev1__DateSigned__c,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( echosign_dev1__Opportunity__r.Date_Contract_Completed__c - echosign_dev1__DateSigned__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( echosign_dev1__Opportunity__r.Date_Contract_Completed__c - echosign_dev1__DateSigned__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( echosign_dev1__Opportunity__r.Date_Contract_Completed__c - echosign_dev1__DateSigned__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)

(FLOOR(( echosign_dev1__Opportunity__r.Date_Contract_Completed__c - echosign_dev1__DateSigned__c )/7)*5)

I had to change the original date to the datesigneddate as my datesigned was a date time format not a date format.

When i run this it is bringing up results ranging from 999 - 1040. I realise i am doing something wrong but am not interely sure what and if i am honest im not too sure what all the integers are doing in the formula to try to work out my mistake,

Any help on fixing this formula or another solution to my problem would be great.

What i am looking for is if i signed a contract today and it was input at 14.06 on monday it would show 2.0 days. However i want to show the decimals for the part days it takes aswell so not to just have whole numbers,

Thanks

Matt