You need to sign in to do that
Don't have an account?
Vorix
Formula to calculate the number of working days between 2 dates
The following formula will calculate the number of working days
(inclusive) between 2 dates. A working day is defined as Monday to
Friday. Even if the start or end dates are a weekend, these are
accommodated.
IF(AND((5  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
((( End_Date__c  Start_Date__c ) + 1) < 7)),
((CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))  (5  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))),
(((FLOOR((( End_Date__c  Start_Date__c )  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) +
(CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
(CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))))
The Start Date and End Date fields are custom in the above example and can be replaced as required. If use of a DateTime field is required then the DATEVALUE function will be required.
I also recommend a simple field validation rule is added to check that the End Date is after the Start Date.
IF(AND((5  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
((( End_Date__c  Start_Date__c ) + 1) < 7)),
((CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))  (5  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))),
(((FLOOR((( End_Date__c  Start_Date__c )  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) +
(CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
(CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))))
The Start Date and End Date fields are custom in the above example and can be replaced as required. If use of a DateTime field is required then the DATEVALUE function will be required.
I also recommend a simple field validation rule is added to check that the End Date is after the Start Date.
This formula seems to work when calculating bt 2 different days. What about taking into account for same day and returns result shoud be a "0"? My result shows a "5" when I entered 9/10/09 and 9/10/09 for both start and end dates. Is there one formula that can include this possiblity?
TY!!
Teacup13
All Answers
Any ideas why the filed would not display the number of business days...Formula was saved just fine, I have double checked it and it looks good to me...Here is what I have:
==============================================================
IF(AND((5  (CASE(MOD( Start_Date__c  DATE(2009, 7, 1), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD( End_Date__c  DATE(2009, 9, 30), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
((( End_Date__c  Start_Date__c ) + 1) < 7)),
((CASE(MOD( End_Date__c  DATE(2009, 9, 30), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))  (5  (CASE(MOD( Start_Date__c  DATE(2009, 7, 1), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))),
(((FLOOR((( End_Date__c  Start_Date__c )  (CASE(MOD( Start_Date__c  DATE(2009, 7, 1), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) +
(CASE(MOD( Start_Date__c  DATE(2009, 7, 1), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
(CASE(MOD( End_Date__c  DATE(2009, 9, 30), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))))
================================================================
Any clues would be more than appreciated. Thank you so much!
Blanka
This formula seems to work when calculating bt 2 different days. What about taking into account for same day and returns result shoud be a "0"? My result shows a "5" when I entered 9/10/09 and 9/10/09 for both start and end dates. Is there one formula that can include this possiblity?
TY!!
Teacup13
Hi Vorix,
This formula seems to work when calculating bt 2 different days. What about taking into account for same day and returns result shoud be a "0"? My result shows a "5" when I entered 9/10/09 and 9/10/09 for both start and end dates. Is there one formula that can include this possiblity?
TY
Teacup13
Hi,
First of all, Thanks for this formula.
Well, I saw someone reported there was a problem when both dates where the same day, so I adjusted the original formula into this one:
IF(Start_Date__c <> End_Date__c, IF( /** Condition */ AND( (5  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ), ((( End_Date__c  Start_Date__c ) + 1) < 7) ), /** Value to YES */ ( (CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))  (5  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) ), /** Value to NOT */ ( ((FLOOR((( End_Date__c  Start_Date__c )  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) + (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) + (CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ) ) ,0)
I hope this helps everyone.
Regards,
Wilmer
How about public holidays? Not all Mondays to Fridays are business/working days.
Is there a solution?
This formula works great, but it will only display a calculated number when the case is closed. I want to track the number of business days an open and closed case are open and build workflows from that date. What would be the best way to modify this formula to calculate:
I'm betting you already found a solution to your problem, but here's the formula I use to calculate an end date based on the current date and lead time. If start date is something other than today, just substitute it and it should work fine:
TODAY() /* today's date */
+ Service_Lead_Days__c /* + the lead time */
+ (2 * FLOOR(Service_Lead_Days__c / 5)) /* + the number of weekend days */
+ CASE(MOD( (Today() + MOD(Service_Lead_Days__c, 5))  DATE (2000 ,1, 1) ,7), /* calculate the day of week for the release date based on 1/1/2000 being Saturday */
0,2, /* + 2 days if the calculated release day would be a Saturday */
1,1, /* + 1 day if the calculated release day would be a Sunday */
0) /* + 0 days if the calculated release day would be a weekday */
+ IF(Service_Lead_Days__c < 5, /* + 2 days for short projects that would span a weekend */
IF(MOD(Today()  DATE (2000 ,1, 1) ,7) + Service_Lead_Days__c > 6, 2, 0),
0)
thanks
This is very cool.
Is there any way of tweeking this to calculate the total days (inclusive of all weekends, holidays etc) from a certain date until the end of that current financial year (31st March)?
I'm looking to create this to then be multiplied by weighted value to feed into an annual revenue forecast as a real weighted annual value of multiyear contracts. This will then combine with the weighted annual values of other multiyear contracts and with the weighted value of contracts ending within that financial year.
Is there any one who can help as my programming experience is only just starting up again after finishing at a very low school level over 10 years ago!
Thanks, Steve
After some further thinking does anyone know if the following (written in laymans terms) is possible?
2011
a. IF start date = 1st April or beyond of current financial year (FY) then calculation = (weighted value / 365) * (31st March  start date)
IF a. not true
b. IF start date = FY(s) prior to current FY and end date = FY(s) ahead of current FY then = total annual weighted value
IF a. and b. not true
c. IF start date = FY(s) prior to current FY and end date = current FY = (weighted value / 365) * (end date  1st April)
2012
same as 2011
2013
same as 2013
etc etc
FYI, the equivalent Apex code for the calculation:
It it possible to change this formula to account for a 6 day business week. I am at a loss of where to start to modify this formula. Also is there any way to incorporate holiday's in a similar fashion as the workingdays function in excel. Thanks for any help.
Thanks for this!
Is anyone able to modify the calculation to calculate the number of working days between 2 days if working days are MonSat (in other words, count all days except Sundays?)
Thanks.
This logic is close to what I was looking for.
In my case I have:
Start Date 1  Date field, End Date 1  Formula Field.
Start Date 2  Date field, End Date 2  Formula Field.
End Date 2 = {Start Date 1 + (3 days) }  but it should not be a Weekend.
If its a weekend, then it has to be a next weekday.
Eg: If "End Date 2 calculates to be  Saturday", the formula should calculate and the answer should be Monday's Date.
On the whole End Dates should have the dates from MF.
Assistance on this is highly appreciated.?
Thanks..!
I am using a formula field for calculating the difference in business hours. For holidays I have included an integer field and updating the holidays through a batch update everyday morning at 1AM. Sometimes the batches fail due to some error of overlapping batches or having more volume or some issue.
Is there any way to do this without the batch. How to set a holidays in field and increment them without a batch job?
Thank You
Mrudula
I am able to get the working days by excluding weekends but I am unable to get the formula to exclude both weekends and company holidays ?
Can any one tell me how to exclude company holidays ?
Date edate = Date.valueOf('2016131');
Date d1 = Date.valueOf(sdate);
Date d2 = Date.valueOf(edate);
integer wDay = d1.daysBetween(d2) ;
integer count=0;
do{
if(sdate.format('EEEE') == 'Saturday') {
count++;
}
if(sdate.format('EEEE') == 'Sunday') {
count++;
}
sdate= sdate.adddays(1);
}while(sdate<=edate);
wDay = wDaycount+1;
System.debug(wDay);
just give the values for sdate and edate by assigning it directly or by passing in function. It will give you the number of working days between two dates.
Please check
Could any one please provide right solution, for this issue?
I need a solution that calculates "0" for two dates the same and then increments from there, but I am unable to find anything anywhere to achieve this.Can anyone assist?
I use this formula to calculate working days between monday and friday :
IF(AND((5  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
((( End_Date__c  Start_Date__c ) + 1) < 7)),
((CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))  (5  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))),
(((FLOOR((( End_Date__c  Start_Date__c )  (CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) +
(CASE(MOD( Start_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
(CASE(MOD( End_Date__c  DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))))
Could you tell me which values must be changed in order to calculate working days between sunday and thursday ?
Thanks
Would anyone know how to calculate working days between two dates? (Including holidays)
From the creation date to the completion date.
This is using the case fields CLOSED_DATEONLY, CREATED_DATEONLY and TODAY().
IF(ISBLANK(CLOSED_DATEONLY),
(
(5 * ( FLOOR( ( TODAY()  DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(
TODAY()  DATE( 1900, 1, 8), 7 ) ) )

(5 * ( FLOOR( ( DATEVALUE(CREATED_DATEONLY)  DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(
DATEVALUE(CREATED_DATEONLY)  DATE( 1900, 1, 8), 7 ) ) )
)
,
(
(5 * ( FLOOR( ( DATEVALUE(CLOSED_DATEONLY)  DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(
DATEVALUE(CLOSED_DATEONLY)  DATE( 1900, 1, 8), 7 ) ) )

(5 * ( FLOOR( ( DATEVALUE(CREATED_DATEONLY)  DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(
DATEVALUE(CREATED_DATEONLY)  DATE( 1900, 1, 8), 7 ) ) )
)
)
Can anyone help me with how to calculate the halfday formula?
I am able to calculate for a full day excluding weekends but I am not able to understand how can I calculate for half day.
Thanks in advance!