I am wandering if there is a way to calculate the number of working days for time period selected by dateselector.
I am using the default dateselector and user can decide if he wont to see ranges for years/months/days or just pick two dates.
My dateselector is using field activity due date. Unfortunately a situation can occur when there are not activities with a due day for every calendar day. E.g. when month of April is selected, there are no activities with due dates for first days of this month and/or by end of the month. Let’s say first available activity in April has due date on 05-04-2020 and last on 25-04-2020. For my formula I would need to get as a result 22 working days for the full month of April instead of 15 if considering the range 05-04-2020 to 24-04-2020.
Any idea how this could be solved?
The date diff calculations are kinda intense.
((DATEVALUE(Date_Time_Assigned__c) - (DATEVALUE(Date_Time_Opened__c)))) -
(CASE(MOD( DATEVALUE(Date_Time_Opened__c) - DATE(1985,6,24),7),
0 , CASE( MOD( DATEVALUE( Date_Time_Assigned__c ) - DATEVALUE(Date_Time_Opened__c), 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1 , CASE( MOD( DATEVALUE( Date_Time_Assigned__c ) - DATEVALUE(Date_Time_Opened__c), 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2 , CASE( MOD( DATEVALUE( Date_Time_Assigned__c ) - DATEVALUE(Date_Time_Opened__c), 7),0,0,1,0,2,0,3,1,2),
3 , CASE( MOD( DATEVALUE( Date_Time_Assigned__c ) - DATEVALUE(Date_Time_Opened__c), 7),0,0,1,0,2,1,2),
4 , CASE( MOD( DATEVALUE( Date_Time_Assigned__c ) - DATEVALUE(Date_Time_Opened__c), 7),0,0,1,1,2),
5 , CASE( MOD( DATEVALUE( Date_Time_Assigned__c ) - DATEVALUE(Date_Time_Opened__c), 7),0,1,2),
6 , CASE( MOD( DATEVALUE( Date_Time_Assigned__c ) - DATEVALUE(Date_Time_Opened__c), 7),6,2,1),
999)
+
(FLOOR(( (DATEVALUE(Date_Time_Assigned__c)) - (DATEVALUE(Date_Time_Opened__c )))/7)*2))
That answer is from this post here: https://trailblazers.salesforce.com/answers?id=9063A000000lBUUQA2
HTH
