Can some explain below formula of excluding weekends counting

Question

Can some explain below formula of excluding weekends counting, I want the explanation I'm not able to understand the below formula.

The formula is :

  CASE(MOD(   ActivityDate - DATE(1985,6,24),7), 

0 , CASE( MOD(TODAY()-ActivityDate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
1 , CASE( MOD( TODAY()-ActivityDate ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
2 , CASE( MOD( TODAY()-ActivityDate ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
3 , CASE( MOD( TODAY()-ActivityDate,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
4 , CASE( MOD( TODAY()-ActivityDate ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
5 , CASE( MOD( TODAY()-ActivityDate ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
6 , CASE( MOD( TODAY()-ActivityDate ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
999) 
+ 
(FLOOR((TODAY()-ActivityDate)/7)*5)-1

Answer 1

For understanding, we can break down the formula into smaller parts and understand each part separately.

The formula consists of three main parts: a CASE function, a FLOOR function, and a subtraction operation.

1. The CASE function evaluates the day of the week of the ActivityDate and the TODAY date, and returns the number of weekdays between them.
2. The FLOOR function calculates the number of full weeks between the ActivityDate and the TODAY date, and multiplies it by 5 to get the number of weekdays in those weeks.
3. The subtraction operation subtracts 1 from the total number of weekdays to exclude the ActivityDate itself.

The below explanation of each part of the formula:

• The formula starts with the CASE function, which has the following syntax:

  CASE(expression, value1, result1, value2, result2,..., else_result)

• The expression is:

  MOD(ActivityDate - DATE(1985,6,24),7)

• This expression calculates the day of the week of the ActivityDate, by subtracting a fixed date (1985-06-24) that is a Monday, and taking the remainder after dividing by 7. The result is a number from 0 to 6, where 0 is Monday, 1 is Tuesday, and so on.

• The value1, result1, value2, result2, and so on, are pairs of values and results that correspond to different cases. For each case, the value is compared with the expression, and if they match, the result is returned. For example, the first case is:

  0 , CASE( MOD(TODAY()-ActivityDate ,7),1,2,2,3,3,4,4,5,5,5,6,5,1)

• This case means that if the expression is 0, which means the ActivityDate is a Monday, then the result is another CASE function, which evaluates the day of the week of the TODAY date, and returns the number of weekdays between the ActivityDate and the TODAY date, excluding the ActivityDate itself.

• For example, if the TODAY date is a Wednesday, the result is 2, because there are two weekdays (Tuesday and Wednesday) between Monday and Wednesday, excluding Monday.

• The other cases are similar, but they have different values and results depending on the day of the week of the ActivityDate. The last case is:

  999

• This case means that if none of the previous cases match, the result is 999, which is an invalid value that indicates an error.

• The formula then adds the result of the CASE function to the result of the FLOOR function, which has the following syntax:

  FLOOR((TODAY()-ActivityDate)/7)*5

• This function calculates the number of full weeks between the ActivityDate and the TODAY date, by subtracting the ActivityDate from the TODAY date and dividing by 7. The FLOOR function rounds down the result to the nearest integer. The result is then multiplied by 5 to get the number of weekdays in those full weeks.

• For example, if the ActivityDate is 2021-09-13 (a Monday) and the TODAY date is 2021-09-29 (a Wednesday), the result is 10, because there are two full weeks (2021-09-13 to 2021-09-19 and 2021-09-20 to 2021-09-26) between them, and 10 weekdays in those weeks.

• The formula then subtracts 1 from the sum of the CASE function and the FLOOR function, to exclude the ActivityDate itself from the count. For example, if the ActivityDate is 2021-09-13 (a Monday) and the TODAY date is 2021-09-29 (a Wednesday), the final result is 11, because there are 12 weekdays between them, excluding the ActivityDate itself.