Blog

Calculate the number of work days per month using a formula field

When building Salesforce reports, it’s sometimes necessary to calculate the number of work days per month, mainly when the report is grouped by month, and your calculation depends on the exact number of work days for individual months.

Team Capacity Example

Knowing the number of work days in a month is essential when calculating the capacity of your resources. Let’s assume that a resource has a capacity of 8 hours per day. For months with 20 working days, a resource would have 160 hours of total capacity. For months with 22 working days, a resource’s total capacity would be 176 hours.

What’s surprising is that many companies we’ve spoken to use 160 hours as their standard estimated monthly capacity. Even for a small team of 20 resources, this assumption can lead to significant inaccuracies. For example, in 2022:

As you can see, if we use an estimated 160 hours per month, we are left with a total of 3200 hours in the year that has not been accounted for. That’s the equivalent of an entire 20-member team’s time not being accounted for an entire month.

Using this formula with your examples

While our example is going to leverage the ForecastDate__c field on the Resource Forecast object, the same principles, logic, and formulas would apply to any other date field on any other standard or custom object.

Our Approach

If you scroll to the bottom of this post and see our formula in its final form, you will notice that it is pretty complex and barely readable. To remove some of this complexity, we’ll first create each piece of logic as a separate formula field. When doing in real-world scenarios, these individual formulas are not required, and the final formula can be used.

Let’s get started

We will use November 2022 as our example month when explaining the below formulas, and the following diagram can be used as a reference.

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 that uses our example date’s year and month and set the day to 1.

DATE(YEAR(ResourceHeroApp__ForecastDate__c), MONTH(ResourceHeroApp__ForecastDate__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 of the month, add a month, then subtract one day.

ADDMONTHS(DATE(YEAR(ResourceHeroApp__ForecastDate__c), MONTH(ResourceHeroApp__ForecastDate__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, then adding the appropriate number of days.

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 a 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. 5 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.

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. 4 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 use this to calculate the total number of full weeks.

((Full_Week_End__c - Full_Week_Start__c) + 1) / 7

First, we subtract the Full Week Start 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. If we start on Sunday and count. Sunday to Monday = 1 day. Monday to Tuesday = 2 days. Tuesday to Wednesday = 3 days. Wednesday to Thursday = 4 days. Thursday to Friday = 5 days. Friday to Saturday = 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, November 26 minus November 6 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 give us the total number of work days 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 what day of the week our month starts on, we can figure out exactly how many days there are until the end of the partial week.

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, and, as a result, the CASE function will return a 4. This means there are 4 work days 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 work days after the last full week similarly.

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 with return a 4, and, as a result, the CASE function will return a 3. This means there are 3 work days 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.

Days_before_first_full_week__c

+

(Full_Weeks__c * 5)

+

Days_after_last_full_week__c

In our example, we add 4 days from the partial week at the beginning, the 15 days in the full weeks, and the 3 days from the partial week at the end, to get a total of 22 work days in November 2022.

Step 9: Creating a single field

As we walked through the logic, we created a separate field for each step. While this works, you could also create a single field that includes all the logic from all steps. Starting with the formula from step 8, work your way backward, replacing our field references with the underlying formulas from those fields.

Once you do that, you can remove all extra spaces and line breaks to maximize both awesomeness and job security resulting in our formula in its final form:

CASE(WEEKDAY(DATE(YEAR(ResourceHeroApp__ForecastDate__c), MONTH(ResourceHeroApp__ForecastDate__c), 1)),1,0,2,5,3,4,4,3,5,2,6,1,7,0,0)+((((((ADDMONTHS(DATE(YEAR(ResourceHeroApp__ForecastDate__c), MONTH(ResourceHeroApp__ForecastDate__c), 1), 1) - 1) - CASE(WEEKDAY((ADDMONTHS(DATE(YEAR(ResourceHeroApp__ForecastDate__c), MONTH(ResourceHeroApp__ForecastDate__c), 1), 1) - 1)),1,1,2,2,3,3,4,4,5,5,6,6,7,0,0)) - ((DATE(YEAR(ResourceHeroApp__ForecastDate__c), MONTH(ResourceHeroApp__ForecastDate__c), 1)) + CASE(WEEKDAY((DATE(YEAR(ResourceHeroApp__ForecastDate__c), MONTH(ResourceHeroApp__ForecastDate__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(ResourceHeroApp__ForecastDate__c), MONTH(ResourceHeroApp__ForecastDate__c), 1), 1) - 1),1,0,2,1,3,2,4,3,5,4,6,5,7,0,0)

To use this in your environment, simply replace all references to ResourceHeroApp__ForecastDate__c with the API name of your date field.

Related support posts

• Salesforce Help: Formula Operators and Functions by Context
• Gain Insights with Reports and Dashboards
• Resource Reports

About the author