Many a times we come across requirements wherein we are supposed to set the due date to a certain number of days from a particular date. Most of the times, organizations insist on setting it to certain number of business days later. This is the point where many admins get stuck figuring out how to add business days to a particular date. I have personally seen many questions asked regarding this in the Salesforce success and developer communities. So thought of writing a post on the same which might help these folks if they have to setup something similar in the future.

Suppose you want to set it to 14 days after a certain date and if you simply add 14 to the value of a date field, Salesforce formula will also include the weekends by default. So how do we override this ?

Well, to do that we need to apply some intelligence to our formula. I will try my best to explain how do we go about doing this.

Use Case: ABC Inc. want to set the Invoice Due Date as 14 business days after the Shipment Date. They would like the system to auto-calculate this and show it to them.

Implementation: Salesforce formula fields are the best way to go about for such scenarios. Now, we will have to first identify the day of the week that shipment date will fall into. To do this we will have to pick up a known date (a date which we know falls on a particular day) in our formula and then accordingly find out the day of the Shipment date. I will use the date “6th Jan, 1900” which I know falls on a Saturday. This date will be represented as DATE(1900,1,6) in our formula and I will refer this date as my “Reference Date”.

So now if we find the difference between the Shipment Date and the Reference Date and then divide it by 7, the remainder should tell us exactly the Day on which the Shipment Date will fall into.

Shipment Date – DATE(1900,1,6)) % 7

The above expression would be represented in the Salesforce formula like this.

MOD(Shipment_Date__c – DATE(1900,1,6), 7)

The Days can be identified as follows based on the value of the above expression

0 = Saturday

1 = Sunday

2 = Monday

3 = Tuesday and so on….

Now, the next step is to identify, how many days needs to be added to Shipment Date based so that the Due Date is 14 business days later. This should be pretty easy now that our formula has the intelligence to identify the day of the week of Shipment Date.

So, if the Shipment Date falls on Saturday, then we need to add 19 days to the Shipment Date so that the Due Date is 14 days later. If you look at the diagram below, let’s assume that the Shipment Date was on 4th, so we need to add 14 business days (shown in green) plus the 5 weekend days which fall in between (Shown in red) that gives us the count of 19.

p1

 

On a similar note, we will have to identify the number of days to be added if Shipment days falls on Sunday or Monday or Tuesday and so on.

So, the days to be added would be like this

Remainder Day of the Week for Shipment Date Number of days to be added
0 Saturday 19
1 Sunday 18
2 Monday 18
3 Tuesday 20
4 Wednesday 20
5 Thursday 20
6 Friday 20

Now, that we have our figures ready we just have to write the formula which would be as below.


IF(MOD( Shipment_Date__c - DATE(1900, 1, 6), 7) > 2,
Shipment_Date__c + 20,
IF(MOD( Shipment_Date__c - DATE(1900, 1, 6), 7) > 0,
Shipment_Date__c + 18, Shipment_Date__c + 19
)
)

I hope the formula is now self-explanatory and hope that now you should be good to setup similar formulas on your own !

Please feel free to comment if you have any doubts regarding this post !