Creating Complex Formulas From Scratch: Work Days per Month

If you’ve navigated the Salesforce ecosystem for any length of time, you know that a web search for the right formula to meet your business needs is all in a day’s work. Digging through forums and knowledge bases for that perfect piece of code has become an integral part of the Salesforce professional’s toolkit.

These quests often lead us to formulas so intricate that they appear to be more art than science. But instead of just copying and pasting these solutions, what if you could learn the art of crafting them yourself? In this guide, we’ll peel back the layers of complex formula fields in Salesforce, building from the basics up to a comprehensive understanding.

Work Days per Month

One such formula that often proves invaluable across various business functions is the calculation of work days per month. Whether it’s for scheduling purposes, project timelines, or financial forecasting, knowing the exact number of work days can provide essential data for more informed decision-making.

If you search the web for this formula, you might stumble upon something that looks 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)

At first glance, this formula might seem impossibly complex. However, there’s a logical structure and method to its madness. We are now going to deconstruct it piece by piece, explaining the purpose of each part and demonstrating how to approach the creation of such formulas systematically.

Let’s Get Started…

In the complex formula above, “MyDateField__c” holds the date for which we aim to determine the total number of workdays within its month. Let’s assume that this field has a value of November 18, 2022. To get a clear view of how to perform our calculations, we’ll break down the month of November as follows:

This breakdown will guide us through the underlying structure of our formula, helping us understand the calculation step-by-step. Note that each step below creates a new formula field on the object that your date field exists on. For example, if “MyDateField__c” is a field on the opportunity object, the below formula fields would be created on the opportunity object.

Step 1: Finding the Month Start

This is, by far, the most straightforward formula we will use as part of this exercise. We simply create a new date formula field that uses our example date’s year and month and set the day to “1”.

Month_Start__c = DATE(YEAR(MyDateField__c), MONTH(MyDateField__c), 1)

Step 2: Finding the Month End

Finding the last day of the month is slightly more complex since we need to account for months with differing numbers of days. The easiest 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: Find the First Day of the First Full Week

Now that we’ve identified the first day of the month, we can use that to find the date of the first Sunday of the first full week. We can do this by determining what day of the week the first of the month falls on and then adding the appropriate 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 will return the day of the week, “1” for a Sunday, “2” for a Monday, and so on…

Using the CASE function, we can specify how many days to add for each weekday. In our example, the first day of the month is Tuesday, November 1st. In this case, the WEEKDAY function will return a “3”, and, as a result, the CASE function will return a “5”. Five days will be added to the month start to give us the date of the first day of the first full week: Sunday, November 6th.

Step 4: Find the Last Day of the Last Full Week

We can find the last day of the last full week in the same way – except in this case, we need to subtract some 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 example, the last day of the month is Wednesday, November 30th. In this case, the WEEKDAY function will return a “4”, and, as a result, the CASE function will also return a “4”. Four days will be subtracted from the month end to give us the date of the last day of the last full week: Saturday, November 26th.

Step 5: Determine the Number of Full Work Weeks

Now that we have the dates of the first day of the first full week and the last day of the last full week, we can calculate the total number of full weeks.

Full_Weeks__c = ((Full_Week_End__c - Full_Week_Start__c) + 1) / 7

First, we subtract the full week start date from the full week end date. This will give us how many days have passed between the two days. Notice that we then add “1” to this result. This ensures that we get the total number of actual days, not the number of days that have passed.

Subtracting one date from another date works the same way as if you counted on your fingers. Think of a single week, starting on Sunday, and count: Sunday to Monday is 1 day, Monday to Tuesday is 2 days, Tuesday to Wednesday is 3 days, Wednesday to Thursday is 4 days, Thursday to Friday is 5 days, and Friday to Saturday is 6 days.

We need to add “1” to ensure we get the total number of days we have touched instead of the number of days that have passed. We can then divide this number by “7” to get the total number of full weeks.

In our example, subtracting November 6 from November 26 gives us 20 days. We add “1” to give us 21 full days, then divide this result by “7” to give us three full weeks. Later, we’ll be able to multiply this by “5” to determine the total number of workdays contained in these full weeks.

Step 6: Determine the Number of Individual Days Before the First Full Week

We need to figure out how many extra days are at the beginning of the month. In other words, how many days at the beginning of the month are work days that are not included in the first full week? We can use the same CASE/WEEKDAY model that we used above. If we figure out on what day of the week our month starts, we can figure out exactly how many days there are until the end 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 first day of the month is a Tuesday. In this case, the WEEKDAY function returns a “3”, resulting in the CASE function returning a “4”. This means there are four workdays in that partial week at the beginning of the month (Tuesday, Wednesday, Thursday, and Friday).

Step 7: Determine the Number of Individual Days After the Last Full Week

We can calculate the number of workdays after the last full week similarly.

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 last day of the month is a Wednesday. In this case, the WEEKDAY function will return a “4”, and, as a result, the CASE function will return a “3”. This means there are three workdays in the partial week at the end of the month (Monday, Tuesday, and Wednesday).

Step 8: Calculate the Total Number of Work Days for the Month

Our final step is to put it all together. We add the number of days in the partial week at the beginning of the month, the number of work days 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: Creating a Single Formula Field

We’ve broken down the logic of each step into separate fields for clarity, but in practice, you don’t need to create each of these fields in Salesforce.

Instead, start with the final formula from Step 8 and go back through each step, swapping out the field names for their actual formulas. By working backwards like this, you’ll piece together the full formula we showed at the beginning of this post.

Summary

Now that you’ve seen how we constructed the ‘work days per month’ formula field, you’re all set to develop your own Salesforce formulas.

Just follow the same approach: start with a clear diagram to map out your logic and break down the formula into distinct logical sections. For each section, create a dedicated formula field to simplify testing and validation. Once you’ve confirmed the accuracy of each section, work backwards to create a single, comprehensive formula.