Apex Code Development You need to sign in to do that
Don't have an account?
Nicole Young 2 Formula field excluding weekends not working accurately
I have a formula field that should be calculating the number of weekdays a case was open. If the case is closed it calculates weekdays between create and closed date; if case is open it calculates age from today and create date. However, I have a closed case that was opened Thursday 8/15/2019 and closed Monday 8/19/2019 and the number that is being returned is zero. Can anyone see my error?
If( IsClosed, (CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7), 0 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE(MOD(ClosedDate - CreatedDate ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE(MOD(ClosedDate - CreatedDate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE(MOD(ClosedDate - CreatedDate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE(MOD(ClosedDate - CreatedDate ,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) - DATE (2000 ,1, 1) ,7)=0), NOT(MOD( Datevalue(CreatedDate) - DATE (2000 ,1, 1) ,7)=1)), 1, 0)), (CASE(MOD(Datevalue(CreatedDate) - DATE(1985,6,24),7), 0 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE(MOD(Today() - Datevalue(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR((Today() - Datevalue(CreatedDate))/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)))
Finding the Number of Business Days Between Two Dates
Calculating how many business days passed between two dates is slightly more complex than calculating total elapsed days. The basic strategy is to choose a reference Monday from the past and find out how many full weeks and any additional portion of a week have passed between the reference date and the date you’re examining. These values are multiplied by five (for a five-day work week) and then the difference between them is taken to calculate business days.
https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5 (https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5)
In this formula, date_1 is the more recent date and date_2 is the earlier date. If your work week runs shorter or longer than five days, replace all fives in the formula with the length of your week.
ClosedDate = date_1
CreatedDate = date_2
All Answers
Finding the Number of Business Days Between Two Dates
Calculating how many business days passed between two dates is slightly more complex than calculating total elapsed days. The basic strategy is to choose a reference Monday from the past and find out how many full weeks and any additional portion of a week have passed between the reference date and the date you’re examining. These values are multiplied by five (for a five-day work week) and then the difference between them is taken to calculate business days.
https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5 (https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5)
In this formula, date_1 is the more recent date and date_2 is the earlier date. If your work week runs shorter or longer than five days, replace all fives in the formula with the length of your week.
ClosedDate = date_1
CreatedDate = date_2