ShowAll Questionssorted byDate Posted
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.
teacup13

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

Buell
Nicely done.
I have been looking for this formula for a while.  Thanks for putting this together.  I was trying to figure out how to calculate just the business days between dates and this works great!
nwing
Has anyone adjusted this to solve for the end date given a start date and number of business days?  I will see to it as time allows and post if I can get it...
Blanka

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

teacup13

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

This was selected as the best answer
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

Wilmer

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

CTU007

How about public holidays? Not all Mondays to Fridays are business/working days.

Is there a solution?

bdowney

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:

• Number of business days from Opened_Date__c to TODAY() when the case status is not equal to "Closed"
• Number of business days from Opened_Date__c to Closed_Date__c (as the current formula reads and calculates) when the status is equal to "Closed"
Your assistance and recommendations are greatly appreciated!
Thanks,
bdowney

Steve O'Neal

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 */
+ (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

stevec134

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 multi-year contracts.  This will then combine with the weighted annual values of other multi-year 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

stevec134

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

pierreeymard@saaspoint

FYI, the equivalent Apex code for the calculation:

```  private Integer getDiffBusinessDays(Date startdt, Date enddt) {
Date tempdate = null;
if (startdt > enddt) {
tempdate = enddt;
enddt = startdt;
startdt = tempdate;
}
Integer i = Math.mod((date.newinstance(1985, 6, 24)).daysBetween(startdt),7); // 24/6/85 was a monday
Map<Integer, Map<Integer, Integer>> m = new Map<Integer, Map<Integer, Integer>> {
0 => new Map<Integer, Integer> { 1 => 2 , 2 => 3 , 3 => 4 , 4 => 5 , 5 => 5 , 6 => 5 },
1 => new Map<Integer, Integer> { 1 => 2 , 2 => 3 , 3 => 4 , 4 => 4 , 5 => 4 , 6 => 5 },
2 => new Map<Integer, Integer> { 1 => 2 , 2 => 3 , 3 => 3 , 4 => 3 , 5 => 4 , 6 => 5 },
3 => new Map<Integer, Integer> { 1 => 2 , 2 => 2 , 3 => 2 , 4 => 3 , 5 => 4 , 6 => 5 },
4 => new Map<Integer, Integer> { 1 => 1 , 2 => 1 , 3 => 2 , 4 => 3 , 5 => 4 , 6 => 5 },
5 => new Map<Integer, Integer> { 1 => 0 , 2 => 1 , 3 => 2 , 4 => 3 , 5 => 4 , 6 => 5 },
6 => new Map<Integer, Integer> { 1 => 1 , 2 => 2 , 3 => 3 , 4 => 4 , 5 => 5 , 6 => 5 }
};
Integer i2 = Math.mod((startdt.daysBetween(enddt)),7);
Integer i3 = (m.get(i)).get(i2);
if (i2 == null || i2 < 1 || i2 > 6) {
if (i >= 0 && i <= 4) { i3 = 1; }
else  { i3 = 0; }
}
i3 = i3 + 5 * (Math.floor( ((Decimal) startdt.daysBetween(enddt)).divide(7,4))).intValue();
if (tempdate != null) i3 *= -1; // negative number of days
return i3;
}```

babranno

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.

ChrisInstantCloud

Thanks for this!

AustinTX26

Is anyone able to modify the calculation to calculate the number of working days between 2 days if working days are Mon-Sat (in other words, count all days except Sundays?)

Thanks.

T-Han

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 M-F.

Assistance on this is highly appreciated.?

Thanks..!

mxravi

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

Aruna
I am also on same boat I need to calculate business days but exclude weekends and company holidays.

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 ?
Jagdeep Juneja 15
Date sdate = Date.valueOf('2016-1-1');
Date edate = Date.valueOf('2016-1-31');
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++;
}
}while(sdate<=edate);

wDay = wDay-count+1;
System.debug(wDay);
Jagdeep Juneja 15
Hi all,
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.
Vishnu Gopakumar
(DATE( YEAR(End_Date__c) , MONTH(End_Date__c) , DAY(End_Date__c) ) - DATE( YEAR( DATEVALUE(Start_Date__c) ) , MONTH( DATEVALUE(Start_Date__c) ) , DAY( DATEVALUE(Start_Date__c) ) )

Hi,

Could any one please provide right solution, for this issue?

Kevin Nielsen
I have implemented this formula, which is appearing in requests for a formula field to calculate business days all over the internet and community forums. However, when the two dates compared are the same date I get a value of "1". I tried Wilmer's solution and I do get "0" when the dates are the same, but when the dates are one day apart the calculation returns "2" and it goes up from there.

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?
Khaled T.
Hello,

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
RodrigoSan77
Hello guys, I hope you are all well!
Would anyone know how to calculate working days between two dates? (Including holidays)
From the creation date to the completion date.
jchen09
Thought to share, I modified the formula to work without a closed 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 ) ) )
)
)
Mayuri Nehul
Hi All,

Can anyone help me with how to calculate the half-day 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.