+ Start a Discussion
scott2scott2 

Business Day Calendar function

Is there an easy way to create a function that returns the date that is X Business Days after a given date?  So for example, if a certain event happened on Oct 3 and I wanted to caluculate what day of the month is 3 business days (Mon-Fri only) later, it would return Oct 8.
 
Thanks in advance.
DevAngelDevAngel
Well, what language are you using?
scott2scott2
Apologies for my ignorance, but I'm not sure what you mean when you ask what language I am referring to.  
 
I'm trying to define a Salesforce custom field that uses a formula to return the date that is X business days out in the future.  For example, if Project Start = October 5, then the formula
 
SFDC_Project_Start__c + 3
 
should return October 10 which is 3 business days later (Saturday and Sunday aren't business days).
 
Does this help clarify what I'm trying to do?
 
Thanks in advance.
 
Scott
 
 
 
DevAngelDevAngel
No worries,

We get questions about java, .net, flex, ajax, php, perl as well as custom formulas and apex code.

Not sure if this is possible with a formula.  You would need a day of the week function that would return 0 for sunday through 6 for saturday, in which case you would be able to calculate the day of the week.  I don't think that particular function exists though, sorry.




RickyGRickyG
Scott -

If you have an edition that can use Apex Code, you might be able to handle it with some code.  You could create a DaysOff object to list all the days off, and use this to calculate (and verify) the date that was a certain number of days off.  This approach would require a bit of code, but could be used to include things like holidays in the calculation.

Hope this helps.
scott2scott2

Rick,

Appreciate the suggestion.  Not sure if I have either the right edition or the expertise.  I'll check whether we can do this or not.

Thanks,

Scott

flescorflescor

Given that SFDC went to the trouble of building in internationalization of the calendar functions, it's surprising that some basic time-based functions (such as revenue scheduling) don't allow use of business days rather than calendar days.  For instance, we could have PS project managers schedule revenue for a three-week consulting job on a daily basis and let SFDC work out how much revenue falls in month 1 and how much in month 2 - except that SFDC doesn't exclude the weekends when it counts.  A missed opportunity to streamline work.

 

RickyGRickyG
But a great suggestion for IdeaExchange.

LRBScarecrowLRBScarecrow
Hello - I believe I had a similar problem and came up with a solution that might at least point you in the right direction. We needed to automatically set a completed date field based on a number entered into a "Number of Days to complete" field. The starting date would be entered and then the number of days, and using those 2 fields, the completed date would populate based on the number of days and it would exclude weekends. You might be able to take this solution and tailor it to fit your needs:

First, created a field that would store the day of the week that the start date fell on (in your case could be the created date for the lead). I did this by creating a formula field "Weekday Code" that would store a number with the following formula:

MOD((FLOOR(VALUE( MID( TEXT( Start_Date__c ),3, 2))/4) + VALUE( MID( TEXT( Start_Date__c ),3, 2)) + VALUE( RIGHT( TEXT( Start_Date__c ), 2)) + CASE(VALUE( MID( TEXT( Start_Date__c ),6, 2)),

1,0,
2,3,
3,3,
4,6,
5,1,
6,4,
7,6,
8,2,
9,4,
10,0,
11,3,
5)),7)

This is that sweet ol' Calendar trick you probably learned in Math class back in the day...but it still works - the output is going to be a number - 0=Sunday, 1=Monday, 2=Tuesday....6=Saturday.

Next, I created a workflow rule that fired every time the "Number of Days" field was NOT blank...and then I put this formula on the "Completed Date" field update for that workflow rule:

(Start_Date__c + (Number_of_Days__c -1)) + ((CEILING(((Weekday_Code__c + (Number_of_Days__c -1))*0.2))/0.2)-5)/2.5

This uses the day of the week found by "Weekday Code" and determines the date that is "Number of Days" business days after the Start Date (including the start date...in order to exclude the start date, simply remove the "-1" from both instances in the formula).

This portion of the formula strictly Calculates the number of weekend days there would be between the Start date and "Number of Days" business days later:

((CEILING(((Weekday_Code__c + (Number_of_Days__c -1))*0.2))/0.2)-5)/2.5