## 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.

- Calculate the number of full weeks between start date and end date and then calculate the number of working days for the full weeks.
- Calculate the remaining days outside of the full weeks
- 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)*: Finally the rounded-down weeks are multiplied by 5 to get the total number of working days.*5*

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.

- Find the day of joining using the reference date 01/01/1900 (Monday).
- Calculate the remaining days outside of the full weeks.
- 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.]

## Leave a Comment