function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
HaigyHaigy 

HELP - Calculate number of days between two dates but excluding weekends from being included

I have 2 fields start date and closed date within a custom object I need to calculate the number of days between the date which I can do by putting in the formula

 

(Date_closed__c – Date_started) – 1

 

But how do make the formula so that it does not include weekends as it should only calculate Mon - Fri

 

Hope someone can help.

 

James

CRMsimpleCRMsimple
Not totally sure of the formula but you first need to calculate the number of weekend days between the two dates and then use the result of that formula to calculate the weekdays by subtracting End Date - Start Date - # of Weekend Days
TCAdminTCAdmin
Hello Haigy,

I have this formula but it took me hours of work. You have to compare the start date to a known date in the past to determine which day of the week it is. Then compare that with the end date using MOD(). You then use that result to determine how many days that difference actually results in. You also have to determine if you want to count the start date as well.
JakesterJakester
Wow - sounds cool!

Share, share! (please!)
HaigyHaigy

There is actually a formula that has been put on salesforce FAQ database to count the number of weekend days there are between two custom dates dates. You then create a formula field to calculate to total number of days between the dates then do a calculation with both of the formulas.

Does any one know how to exclude bank holiday dates from the formula?

Formula 1

(EndDate - StartDate) + 1

Formula 2

Formula for Number of Weekend Days between two dates:
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 )

Formula 3

Calculate the two other formulas. Formula 1 - Formula 2.
thinkfirst29thinkfirst29

I use the following formula...works as long as the close/open dates are not on weekends. Does not count the first day as a day.

 

 

If(ISNULL(ClosedDate),

(today() - datevalue(CreatedDate) - 2*( floor((today() - DATE(1985, 6, 24))/7) - floor((datevalue(CreatedDate) - DATE(1985, 6, 24))/7))),

(datevalue(ClosedDate) - datevalue(CreatedDate) - 2*( floor((datevalue(ClosedDate) - DATE(1985, 6, 24))/7) - floor((datevalue(CreatedDate) - DATE(1985, 6, 24))/7))))

msatmsat

If(ISNULL(now()), (today() - datevalue(CreatedDate) - 2*( floor((today() - DATE(1900, 1, 1))/7) - floor((datevalue(CreatedDate) - DATE(1900, 1, 1))/7))), (datevalue(now()) - datevalue(CreatedDate) - 2*( floor((datevalue(now()) - DATE(1900, 1, 1))/7) - floor((datevalue(CreatedDate) - DATE(1900, 1, 1))/7))))+1

 

 

Thanks

MSAT