function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nicole Young 2Nicole 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 
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!
Alain CabonAlain Cabon
@Nicole Young

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): 
(5 * ( FLOOR( ( date_1 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_1 - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( date_2 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_2 - DATE( 1900, 1, 8), 7 ) ) )

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: 
(5 * ( FLOOR( ( ClosedDate - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( ClosedDate - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( CreatedDate - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( CreatedDate - DATE( 1900, 1, 8), 7 ) ) )
+
(5 * ( FLOOR( ( L_Hold_Status__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( L_Hold_Status__c - DATE( 1900, 1, 8), 7 ) ) ) 
- 
(5 * ( FLOOR( ( E_Hold_Status__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( E_Hold_Status__c - DATE( 1900, 1, 8), 7 ) ) )

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.