Apex Code Development You need to sign in to do that
Don't have an account?
Matthew Bracewell Working Days between 2 dates
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
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
The problem with the above formula is that it is using DATE and converting all of your DATETIMEs and therfore dropping your partial days. I don't think that there is a good way to do what you want via a formula. I think the best way would be to have a trigger on the Opportunity so that when the Date_Contract_Completed__c field is set that you do the math in the trigger. I would get a difference between the completed date and the start date. Then iterate over all the days in between (using .addDays(1)) and determining if it is a work day. If it is not a work day then I would subtract a day from your difference.