Salesforce Business Date Formula

Just putting this here so I remember it if I ever need it in the future.

There was a Trailblazer community post I came across and thought “I can do that” - and it looks like I did. The OP had a formula he was using to calculate 5 business days before an event date and now needed to change the calculation to 9 days prior. The OP wasn’t sure how to do it.

The issue was that with the date calculation, the OP needed to adjust the number of weekend days for some of the days of the week as they would now cross two weekends (4 days) and not just one weekend (two days). I will admit that I had to do some searching to help figure out how the Salesforce date formulas worked, but it was easily found and here’s the result. Sunday is “0” and you can figure out the rest. It then ends with the last two days of the week using the (2 + 9) formula since there’s no point in repeating it for them. And you get an error if there are an even number of arguments in the CASE formula.

Big_Event_date__c -
MOD( Big_Event_Date__c - DATE( 1900, 1, 7 ), 7 ) ,
0, (3 + 9),
1, (4 + 9),
2, (4 + 9),
3, (4 + 9),
4, (4 + 9),
(2 + 9))

If you have stumbled across this post for a date calculation, I hope it helps.


See also