If you’ve been using Salesforce for a while, you’re probably used to searching the web for the right formulas to make your business processes work. It’s a common part of the job to go through forums and articles to find the perfect piece of code.
Sometimes, these formulas can be pretty complicated, almost like a form of art. Instead of just copying and pasting them, what if you could learn how to create these formulas yourself? In this guide, we’ll break down the complexity of formula fields in Salesforce, starting with the basics and working our way up to help you understand them better.
Work Days Per Month
A useful formula in business is figuring out how many work days there are in a month. This is handy for scheduling, project planning, and financial predictions. If you look it up online, you might find a formula 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 look, this formula might seem really complicated. But don’t worry, there’s a method to the madness. Let’s break it down step by step, explaining each part and showing how to create such formulas in a logical way.
Let’s Get Started…
Imagine you have a complicated formula, and there’s a specific date stored in a field called “MyDateField__c.” For example, let’s say this date is November 18, 2022. Now, we want to figure out the total number of workdays in the month of November.
To make it easier to understand, let’s break down November into days and calculate how many of them are workdays.
This breakdown will walk us through how our formula works, breaking it down step by step. Each step creates a new formula field on the same object where your date field is located. For instance, if “MyDateField__c” is a field on the opportunity object, the formula fields mentioned below will be added to the opportunity object.This breakdown will walk us through how our formula works, breaking it down step by step. Each step creates a new formula field on the same object where your date field is located. For instance, if “MyDateField__c” is a field on the opportunity object, the formula fields mentioned below will be added to the opportunity object.
Step 1: Find the Month Start
Create a date formula setting the day to “1” for the given month and year.
Month_Start__c = DATE(YEAR(MyDateField__c), MONTH(MyDateField__c), 1)
Step 2: Find the Month End
Find the first day of the month, add a month, and subtract one day to get the last day of the month.
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
Identify the day of the week for the first day of the month and add the necessary days to get to the first Sunday of the first full week.
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)
Step 4: Find the Last Day of the Last Full Week
Similar to step 3, subtract some days from the last day of the month to get the last day of the last full week.
Full_Month_End__c – CASE(WEEKDAY(Month_End__c),
1,1,
2,2,
3,3,
4,4,
5,5,
6,6,
7,0,
0)
Step 5: Determine the Number of Full Work Weeks
Calculate the total number of days between the first and last full weeks, add 1, and divide by 7 to get the total number of full weeks.
Full_Weeks__c = ((Full_Week_End__c – Full_Week_Start__c) + 1) / 7
Step 6: Determine the Number of Individual Days Before the First Full Week
Find out how many workdays are at the beginning of the month before the first full 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)
Step 7: Determine the Number of Individual Days After the Last Full Week
Calculate 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)
Step 8: Calculate the Total Number of Work Days for the Month
Add the number of days in the partial week at the beginning, the work days in full weeks, and the days in the partial week at the end.
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
Combine all the steps into one formula, replacing field names with their actual formulas.
So, in a nutshell, we’re figuring out the start and end of the month, finding the first and last days of complete work weeks, counting the number of full work weeks, and then considering the remaining days at the beginning and end of the month to get the total workdays for that month.
Summary
Now that you’ve learned how we made the ‘work days per month’ formula, you’re ready to create your own formulas in Salesforce.
Just do the same thing: begin with a clear diagram to organize your thoughts and divide the formula into smaller logical parts. Make a separate formula field for each part to make testing easier. Once you’ve double-checked each part and know it’s right, put it all together to make one complete formula.
For more updates about Salesforce, visit us on www.thesalesforcemaster.com
