You need to sign in to do that
Don't have an account?
Nicole Young 2
Custom Age field
I have a formula field that I can't get to work properly. I want it to calculate the days between open and closed but subtract the time frame if it was ever in Hold status. When I dont have day
s in the hold status fields it calculates accurately. When I add dates to those fields it only subtracts one day. Any help would be greatly appreciated!
If( IsClosed, (CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7), 0 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE(MOD(((ClosedDate-CreatedDate)-( L_Hold_Status__c - E_Hold_Status__c )) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR((ClosedDate-CreatedDate)/7)*5) - IF(OR( NOT(MOD( Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) - DATE (2000 ,1, 1) ,7)=0), NOT(MOD( Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)), (CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7), 0 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE(MOD(Today() - Datevalue(CreatedDate-(L_Hold_Status__c -E_Hold_Status__c)) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR((Today() - Datevalue(CreatedDate-(L_Hold_Status__c - E_Hold_Status__c)))/7)*5) - IF(OR( NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=0), NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)))
s in the hold status fields it calculates accurately. When I add dates to those fields it only subtracts one day. Any help would be greatly appreciated!
This formula calculates the number of business days by default between the closed date and the created date.
(FLOOR((ClosedDate-CreatedDate)/7)*5) : complete weeks of 5 business days per week ( 7 days ) between Closed and Created dates.
The complicated parts are just to calculate the number of extra days outside the complete weeks according a precise day of week.
Did you change an existing formula or did you create the above formula from scratch?
Finding the Number of Business Days Between Two Dates (simpler and sufficient):
https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5 (https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5)
You can try this simpler formula:
In this formula, ClosedDate with L_Hold_Status__c are the more recent dated and CreatedDate with E_Hold_Status__c are the earlier dates.
If your work week runs shorter or longer than five days, replace all fives in the formula with the length of your week.
The four dates always exist in this formula but that is not perhaps your case.