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.

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 !

Any specific reason of using “6th Jan, 1900” date?

LikeLike

Hi Vk,

It’s just a random date in the past that I have taken. You can use any known day which is in the past and wouldn’t overlap with any of the other dates that you use in the formula.

LikeLike

Reblogged this on sfdcFanBoy and commented:

Good Post by Force-Base.Com!

LikeLike

Thank you for sharing this useful formula and your logical approach Akhil. I am trying to figure out how I can display data on a report for the past five business days. This report should exclude data from the weekend (Saturday and Sunday). Your thoughts and explanation would be most appreciated!

LikeLike

Hi Minhaj,

Can you clarify further about your requirement ? Like if I run the report today which is a Sunday, should it return the data only for the last Wednesday to Friday ? or from last Monday – Friday ?

LikeLike

Hi Akhil, thank you for responding. I am trying to create a report that shows data for Case records for the last 30 business days (that excludes Saturday and Sunday). So I modified your very useful formula to create a formula field on the Case object. This formula field returns the date as it was 30 business days ago. So thank you for that! You got me this far. Now I need to compare this date with the ‘Date/Time Opened’ field on the Case object. So that I can use the True/False value returned as a filter in my report. The challenge is that the ‘Date/Time Opened’ field (API Name: CreatedDate) also shows the time. How can I compare it with the results of our formula field (Which only returns Date) to give a true/false value so that I can use it in my report as a filter? Thank you again for looking into it.

LikeLiked by 1 person

Hi Akhil,

can you provide me the formula to calculate the no of cases for the business days ,which should exclude the Weekend(sat & Sun)

LikeLike

Hi Minhaj,

You just have to use the DATEVALUE() function over the CreatedDate field like this –> DATEVALUE(CreatedDate).

That will return just the Date value from the Datetime field.

LikeLike

Thank you! I’ll try this first thing in the morning. Really appreciate your help!

LikeLike

Happy to Help Minhaj 🙂

LikeLike

Good morning Akhil, please allow me to explain my status:

Requirement: Create a report on Cases that shows data for the last 30 Business Days.

What I Did:

1. I used your formula to create a formula field (30 Duty Days Ago) which subtracts 30 duty days from the current date.

2. The above mentioned formula field (30 Duty Days Ago) returns the date at it was 30 duty days ago in a Date format.

3. Question: How should I use the filters on my report to use the above so that the report shows cases for the last 30 Business Days?

Thanks!

Minhaj

LikeLike

I need to add number of business hours in opendate field. Also want to exclude holidays.

LikeLike

Akhil,

Do you know how might I do this in a Visualforce email template. Currently I need to provide an expected delivery date based on a ship date. The shipping method is always 2 days. So I have the following which adds the days w/o issue. However I need to exclude Saturday and Sunday….

LikeLiked by 1 person

Hi Akhil, Thank you so this detailed explanation. It really helped a lot.

Do you have any suggestions on how to exclude holidays as well? Or has it to be done via code?

Any suggestions are appreciated.

Thank you!

LikeLike

Hello Akhil,

I have the same requirement that you have explained, just that the # of days to add is a variable being read from a record field. So its hard to determine how many more days to add. Do you have a solution for this?

Thanks

LikeLike

Hi Akhil,

Thank you for your post. I have a request that is almost the same. How would you represent a specific date minus 1 business day? Weekends excluded.

Thanks,

Jessica

LikeLike

Hi,

I assume it is not considering the Holidays others than the weekends. Is that right

LikeLike

Hi,

Sorry this may not be relevant but am curious do you think it’s possible to leverage your formula so it calculates the age of the case based on a specific timezone for example SGT?

LikeLike