Tips & Trics: Calculate work days per month in Salesforce
If you’ve been using Salesforce for a while, you’re probably familiar with the routine of searching the web to find the right formula for your business needs. It’s a common task for Salesforce professionals to spend time searching through forums and knowledge bases to discover the perfect piece of code.
Sometimes, the formulas you find are so complex that they seem more like a form of art than a straightforward science. Instead of simply copying and pasting these solutions, what if you could learn how to create them yourself? This guide will take you through the process of understanding and building complex formula fields in Salesforce, starting from the basics and gradually building up to a comprehensive level.
Work days per month
A useful formula in many business scenarios is the calculation of work days per month. Whether you’re planning schedules, managing project timelines, or forecasting finances, having the accurate count of work days is crucial for making well-informed decisions.
If you try to find this formula online, you might come across something like this:
CASE(WEEKDAY(DATE(YEAR(MyDateField__c), MONTH(MyDateField__c), 1)),1,0,2,5,3,4,4,3,5,2,6,1,7,0,0)+((((((ADDMONTHS(DATE(YEAR(MyDateField__c), MONTH(MyDateField__c), 1), 1) – 1) – CASE(WEEKDAY((ADDMONTHS(DATE(YEAR(MyDateField__c), MONTH(MyDateField__c), 1), 1) – 1)),1,1,2,2,3,3,4,4,5,5,6,6,7,0,0)) – ((DATE(YEAR(MyDateField__c), MONTH(MyDateField__c), 1)) + CASE(WEEKDAY((DATE(YEAR(MyDateField__c), MONTH(MyDateField__c), 1))),1,0,2,6,3,5,4,4,5,3,6,2,7,1,0))) + 1) / 7) * 5)+CASE(WEEKDAY(ADDMONTHS(DATE(YEAR(MyDateField__c), MONTH(MyDateField__c), 1), 1) – 1),1,0,2,1,3,2,4,3,5,4,6,5,7,0,0)
Initially, this formula may appear extremely complicated. Yet, there’s a logical structure and method to its complexity. We’re about to break it down step by step, clarifying the purpose of each segment and illustrating how to systematically create such formulas.
High level overview:
In the intricate formula mentioned earlier, “MyDateField__c” contains the date for which we want to figure out the total number of workdays within its month. Let’s suppose this field is set to November 18, 2022. To understand how to carry out our calculations, let’s break down the month of November like this:
Step 1: Determining the Month Start
This is the simplest formula we’ll use in this exercise. We just need to create a new date formula field that incorporates the year and month from our example date, with the day set to “1”.
Month_Start__c = DATE(YEAR(MyDateField__c), MONTH(MyDateField__c), 1)
Step 2: Determining the Month End
Figuring out the last day of the month is a bit more involved because we have to consider months with varying numbers of days. The simplest way to do this is to find the first day of the month, add a month, and then subtract one day.
Month_End__c = ADDMONTHS(DATE(YEAR(MyDateField__c), MONTH(MyDateField__c), 1), 1) – 1
Step 3: Identify the First Day of the First Full Week
Having pinpointed the first day of the month, we can use it to locate the date of the initial Sunday in the first complete week. This involves figuring out the day of the week the first of the month lands on and then adding the necessary number of days.
Full_Week_Start__c = Month_Start__c + CASE(WEEKDAY(Month_Start__c), 1,0, 2,6, 3,5, 4,4, 5,3, 6,2, 7,1, 0)
The WEEKDAY function provides the day of the week, with “1” for Sunday, “2” for Monday, and so forth…
By employing the CASE function, we can define the number of days to add for each weekday. In our instance, if the first day of the month is Tuesday, November 1st, the WEEKDAY function will yield “3”. Consequently, the CASE function will yield “5”, leading to the addition of five days to the month’s start, resulting in the date of the first day of the first full week: Sunday, November 6th.
Step 4: Determine the Last Day of the Last Full Week
Locating the last day of the last full week follows a similar approach. However, in this instance, we have to subtract a certain number of days from the last day of the month.
Full_Month_End__c – CASE(WEEKDAY(Month_End__c), 1,1, 2,2, 3,3, 4,4, 5,5, 6,6, 7,0, 0)
In our illustration, the final day of the month is Wednesday, November 30th. Here, the WEEKDAY function will yield “4”, causing the CASE function to return the same value. Subsequently, four days will be deducted from the end of the month, resulting in the date of the last day of the last full week: Saturday, November 26th.
Step 5: Calculate the Quantity of Full Work Weeks
With the dates of the initial day of the first complete week and the last day of the last complete week in hand, we can now determine the total number of full weeks.
Full_Weeks__c = ((Full_Week_End__c – Full_Week_Start__c) + 1) / 7
Initially, we subtract the start date of the full week from the end date of the full week. This calculation provides the number of days that have elapsed between the two dates. Importantly, we then add “1” to this result. This addition ensures that we obtain the total count of actual days, not just the count of days that have passed.
The process of subtracting one date from another is akin to counting days on your fingers. Envision a single week commencing on Sunday and count: Sunday to Monday equals 1 day, Monday to Tuesday equals 2 days, and so forth. We need to include “1” to capture the total number of days touched, not just the days that have passed. Subsequently, we divide this total by “7” to ascertain the overall number of full weeks.
In our example, subtracting November 6 from November 26 results in 20 days. By adding “1,” we get 21 full days, and dividing this figure by “7” yields three full weeks. This value can later be multiplied by “5” to ascertain the total number of workdays within these full weeks.
Step 6: Calculate the Count of Individual Days Before the First Full Week
Now, we must determine the additional days at the start of the month. Essentially, we need to find out how many workdays at the beginning of the month fall outside of the first full week. We can apply the same CASE/WEEKDAY approach utilized earlier. By determining the day of the week the month begins, we can precisely calculate the number of days until the conclusion of the partial week.
Days_before_first_full_week__c = CASE(WEEKDAY(Month_Start__c), 1,0, 2,5, 3,4, 4,3, 5,2, 6,1, 7,0, 0)
In our example, the month begins on a Tuesday. Consequently, the WEEKDAY function provides a value of “3”, causing the CASE function to yield “4”. This signifies that there are four workdays in that partial week at the start of the month, specifically on Tuesday, Wednesday, Thursday, and Friday.
Step 7: Calculate the Count of Individual Days After the Last Full Week
Similarly, we can compute the number of workdays after the last full week.
Days_after_last_full_week__c = CASE(WEEKDAY(Month_End__c), 1,0, 2,1, 3,2, 4,3, 5,4, 6,5, 7,0, 0)
In our example, the final day of the month falls on a Wednesday. In this scenario, the WEEKDAY function will produce a “4”, leading the CASE function to output “3”. Consequently, there are three workdays in the partial week at the end of the month, specifically on Monday, Tuesday, and Wednesday.
Step 8: Compute the Overall Count of Workdays for the Month
In the last step, we consolidate all the components. This involves adding the number of days in the partial week at the beginning of the month, the count of workdays in the full weeks, and the number of days in the partial week at the end of the month.
Work_Days_Per_Month__c = Days_before_first_full_week__c + (Full_Weeks__c * 5) + Days_after_last_full_week__c
Step 9: Formulating a Unified Formula Field
While we’ve dissected the logic into distinct fields for clarity, in reality, you don’t have to generate each of these fields individually in Salesforce.
Instead, commence with the ultimate formula derived in Step 8 and revisit each step, replacing the field names with their corresponding formulas. By proceeding in reverse like this, you’ll seamlessly assemble the comprehensive formula introduced at the start of this discussion.
Summary
Having observed the construction of the ‘work days per month’ formula field, you’re now equipped to craft your own Salesforce formulas.
Simply adhere to the same methodology: initiate the process with a coherent diagram to outline your logic, and segment the formula into distinct logical components. For each segment, establish a dedicated formula field to facilitate testing and validation. Once you’ve verified the correctness of each segment, proceed in reverse to generate a unified, comprehensive formula.
