Business Challenge:

Create a Formula field to get the duration between two date fields and it should exclude weekends.

Solution:

Create two Date Fields(  Date_ of_ Joining_c and Last_ Working_ Day_c) and one Formula field(Working_Days_c) in Employee Object.

Create the following formula using the formula field.

*(FLOOR((Date_of_Joining_c- Last_Working_Day_c )/7)5)

+

CASE(MOD(  Date_of_Joining__c - DATE(1990,01,01),7),  

  0 , CASE( MOD( Date_of_Joining_c- Last_Working_Day_c,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 

  1 , CASE( MOD( Date_of_Joining_c- Last_Working_Day_c,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 

  2 , CASE( MOD( Date_of_Joining_c- Last_Working_Day_c,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 

  3 , CASE( MOD(Date_of_Joining_c -Last_Working_Day_c,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 

  4 , CASE( MOD( Date_of_Joining_c -Last_Working_Day_c,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 

  5 , CASE( MOD( Date_of_Joining_c- Last_Working_Day_c,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 

  6 , CASE( MOD( Date_of_Joining_c -Last_Working_Day_c,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 

  999) 

   This formula is built in three steps.

1. Calculate the number of full weeks between start date and end date and then calculate the number of working days for the full weeks.
2. Calculate the remaining days outside of the full weeks
3. Then sum the results from the first two steps

Step 1:

*FLOOR((Date_of_Joining_c - Last_Working_Day_c )/7)5

• Date_of_Joining - Last_Working_Day: This part of the formula calculates the total number of  days between the two date fields .
• (Date_of_Joining_c - Last_Working_Day_c )/7: The total number of days is further divided by 7 and it gives the total number of weeks.
• Floor: This function rounds down the result of the division. For example: If the total number of days is 8 to 13, then it rounds down to 1 week.
• *FLOOR((Date_of_Joining_c - Last_Working_Day_c )/7)5: Finally the rounded-down weeks are multiplied by 5 to get the total number of working days.

On the whole this formula calculates the number of weeks between the "Date_of_Joining_c" and "Last_Working_Day_c" dates, rounds down the result to the rounded-down whole number, and then multiplies it by 5. The multiplication by 5 is done to convert the weeks into the corresponding number of working days in a week (5-day work week). 

Step 2:

CASE(MOD( Date_of_Joining__c - DATE(1990,01,01),7),  

  0 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 

  1 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 

  2 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 

  3 , CASE( MOD(Date_of_Joining_c - Last_Working_Day_c,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 

  4 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 

  5 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 

  6 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 

  999) 

This is having further sub steps.

1. Find the day of joining using the reference date 01/01/1900 (Monday).
2. Calculate the remaining days outside of the full weeks.
3. Based on the joining date and the remaining days outside of full weeks, find the remaining working days.

Step 2a.

Find the day of joining using the reference date 01/01/1900. Here 01/01/1900 is Monday. 

MOD(  Date_of_Joining__c - DATE(1990,01,01),7)

For each day of the week, the formula derives a specific value based on the conditions for Monday 0, for Tuesday 1 etc.

Step 2b.

         Calculate the number of days for the incomplete week.

MOD( Date_of_Joining_c- Last_Working_Day_c,7)

The above formula returns - 

• 0 means, the last working day is Monday.
• 1 means, the last working day is Tuesday. 
• 2 means, the last working day is Wednesday. 
• 3 means, the last working day is Thursday. 
• 4 means, the last working day is Friday and so on.

Step 2c.

         Calculate the number of working days for the incomplete week.

If the start date is derived from step 2a , the following formula executes - 

CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,2,2,3,3,4,4,5,5,5,6,5,1)

• 1 means, the last working day is Tuesday. So, the remaining working days - 2 (Monday and Tuesday)
• 2 means, the last working day is Wednesday. So, the remaining working days - 3 (Monday, Tuesday and Wednesday)
• 3 means, the last working day is Thursday. So, the remaining working days - 4 (Monday, Tuesday, Wednesday and Thursday)
• 4 means, the last working day is Friday. So, the remaining working days - 5 (Monday, Tuesday, Wednesday, Thursday and Friday)
• 5 means, the last working day is Saturday. So, the remaining working days - 5 (Monday, Tuesday, Wednesday, Thursday and Friday)
• 6 means, the last working day is Sunday. So, the remaining working days - 5 (Monday, Tuesday, Wednesday, Thursday and Friday)
• 0 (default at the end) means the last working day is also Monday. So, the remaining working days - 1 (Monday)

Similarly, for the day of joining is Tuesday - 

CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,2,2,3,3,4,4,4,5,4,6,5,1)

The following is the formula for all the days.

CASE(MOD( Date_of_Joining__c - DATE(1990,01,01),7),

  0 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,2,2,3,3,4,4,5,5,5,6,5,1),   -- Monday

  1 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,2,2,3,3,4,4,4,5,4,6,5,1),   -- Tuesday

  2 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,2,2,3,3,3,4,3,5,4,6,5,1),  -- Wednesday

  3 , CASE( MOD(Date_of_Joining_c - Last_Working_Day_c,7),1,2,2,2,3,2,4,3,5,4,6,5,1),  -- Thursday

  4 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,1,2,1,3,2,4,3,5,4,6,5,1),  -- Friday

  5 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,0,2,1,3,2,4,3,5,4,6,5,0),  -- Saturday

  6 , CASE( MOD( Date_of_Joining_c - Last_Working_Day_c,7),1,1,2,2,3,3,4,4,5,5,6,5,0),  -- Sunday

  999)  

• Final “CASE” statement at the end (‘999’) is used as a default value if none of the conditions meet. This never happens.

Note:

MOD Function is used to calculate the remainder when one number is divided by another.

FLOOR Function is used to round down a number to the nearest integer that is less than or equal to the original number.]