Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
666Chicago666Chicago 

Formula for tracking ONLY Business Days

Does anyone have a lead age formula that only counts business days and excludes the weekend?  It would be great if the formula also takes into consideration holidays.


thanks,
p
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

What you could do in your case is some working variation of:

"(Today()-[Created date field])-((CEILING(((Weekday_Code__c + (Today()-[Created date field]))*0.2))/0.2)-5)/2.5"

You'd want to make sure the "Weekday Code" field is based off of the created date for the lead.

I'm sure you'll probably have to play around with it to get it tailored to your fields/needs, but the logic should work the same way.
JakesterJakester
Wow! Bravo on this answer!! You rock!
ZomerfeldZomerfeld
Actually this didn't work for me that well, it was somewhat inconsistent.

What I did find in the solutions part of SalesForce itself was this solution:
https://na1.salesforce.com/_ui/training/help/pub/UserEdSolution?id=50130000000Lcle&retURL=https%3A%2F%2Femea.salesforce.com%2F_ui%2Ftraining%2Fhelp%2FCombinedSearchPage%3Fstr%3D%2522weekdays%2522&ps=1&orgId=00D000000000062

IT took a while, as they tagged it "weekdays" and not "business days".

Give it a try.
JakesterJakester

Here's the code that Zomerfeld found:

Code:
CASE( MOD( StartDate__c - DATE( 1985, 6, 24 ) , 7) , 0

, CASE( MOD( EndDate__c - StartDate__c, 7 ),1,0,2,0,3,0,4,0,5,1,6,2,0 ), 1

, CASE( MOD( EndDate__c - StartDate__c, 7 ),0,0,1,0,2,0,3,0,4,0,5,2,2 ), 2

, CASE( MOD( EndDate__c - StartDate__c, 7 ),0,0,1,0,2,0,3,1,2), 3

, CASE( MOD( EndDate__c - StartDate__c, 7 ),0,0,1,0,2,1,2), 4

, CASE( MOD( EndDate__c - StartDate__c, 7 ),0,0,1,1,2), 5

, CASE( MOD( EndDate__c - StartDate__c, 7 ),0,1,2), 6

, CASE( MOD( EndDate__c - StartDate__c, 7 ),6,2,1)

, 666 )

+

( FLOOR( ( EndDate__c - StartDate__c ) / 7 ) * 2 )


 
TehNrdTehNrd
FYI that code above doesn't quite work. I have not had a chance to isolate the issue but if you enter a start date of 1/8/2009 and an end date of 2/3/2009 it returns 8 weekdays between the two when it should be 18.
Message Edited by TehNrd on 02-03-2009 02:07 PM
JakesterJakester

You're absolutely right. I just double-checked everything, and it simply doesn't work. I only provided the source code from the Salesforce help link, so the issue is with the example code. It's pretty significantly off, too. I tried a few other end dates, and they were always wrong. For example, 1/1/09 to 1/15/09 returned a value of 4.

 

I found another thread that I bookmarked a while back and the formula from this thread seems to work quite well. Depending on whether you want to include the start and end dates, you might want to subtract a couple of days off the end result.
lpadminlpadmin

Try this. It's works for us.

Date1 is the earlier of the 2 in the equation.

Good Luck

 

 

MAX ( 0,
( Date2 - Date1+ CASE ( MOD (Date2- DATE( 1900,1,6 ),7),0,1,1,1,0 ) ) -
( ( FLOOR ( ( ( Date2- Date1 ) / 7 ) ) * 2 ) +
CASE (
MOD ( Date1 - DATE( 1900,1,6 ),7),
0,CASE( MOD ( Date2 - DATE( 1900,1,6 ),7),0,1,2 ),
1,CASE( MOD ( Date2 - DATE( 1900,1,6 ),7),0,2,1 ),
IF ( MOD ( Date1 - DATE( 1900,1,6 ),7) - MOD ( Date2 - DATE( 1900,1,6 ),7) <= 0 ,0,
IF ( MOD ( Date2 - DATE( 1900,1,6 ),7) = 0,1,2 ) ) ) ) )
Rup3Rup3

Rather complex.

 

This is what I use :

EndDate__c - StartDate__c - 2*( floor((EndDate__c - DATE(2009, 9, 14))/7) - floor((StartDate__c - DATE(2009, 9, 14))/7) )
 
 HTH,
Rup 
 


Kerry TownsendKerry Townsend
Hi JCM
 
This was really helpful, thanks.


fifedog15fifedog15
Rup3 - That is brilliant!  I have no idea how that works, but that's beautifly simple!

And I would concur with Jakster and TehNrd that one from SFDC doesn't work.