So I wrote some code to handle this recently. It is completely configurable, and as efficient as can be.
The most important thing is to set the isWorkingDay
array with the right flags (index 0 is Monday) and to make sure workingDaysInWeek
matches the number of true flags in the array. After that the method will return a value for you.
//array of seven boolean indicating working days, Monday is index 0
private static final List<Boolean> isWorkingDay;
//count of the number of working days in the array
private static final Integer workingDaysInWeek;
static {
//my real implementation uses the Salesforce BusinessHours Object to populate this array
isWorkingDay = new List<Boolean> { true, true, true, true, true, false, false };
//You will have real problems if this number does not match the number of true flags in the array
workingDaysInWeek = 5;
}
// Monday is January 1st, 1900.
private static final Date monday = Date.newInstance(1900, 1, 1);
private static Integer getDayOfWeek(Date value) {
return Math.mod(monday.daysBetween(value), 7);
}
public static Integer getWorkingDays(Date startDate, Date endDate) {
//save some calculations when the number of working days is 0
if(workingDaysInWeek == 0 || startDate == null || endDate == null) {
return 0;
} else {
Integer difference = startDate.daysBetween(endDate);
if(difference == 0) {
//If the 2 dates are the same day check if the day is a working day or not
return isWorkingDay[getDayOfWeek(startDate)] ? 1 : 0;
} else if(workingDaysInWeek == 7) {
//when every day is a working day return the difference
return difference;
} else {
//The guts of the solution
Integer wholeWeeks = Math.floor(difference / 7).intValue();
Integer workingDays = wholeWeeks * workingDaysInWeek;
Integer dayOfWeek = getDayOfWeek(endDate);
for(Integer remainder = Math.mod(difference, 7); remainder >= 0; remainder--) {
if(isWorkingDay[dayOfWeek]) {
workingDays++;
}
dayOfWeek--;
if(dayOfWeek < 0) {
dayOfWeek = 6;
}
}
return workingDays;
}
}
}
You are correct - Looking at the SFDC Object reference, Case.closedDate
is a DateTime field so line 2 and line 4 will break
Looking at line 2 and line 4 - these are manipulating days, not seconds so I'd rewrite line 2 and line 4 as:
Line 2
( 5 * FLOOR( ( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8) ) / 7) +
Line 4
MOD( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8), 7) +
You should go to the doc and use the feedback form to point this out to SFDC
Best Answer
Finding the Number of Business Hours Between Two Date/Times The formula for finding business hours between two Date/Time values expands on the formula for finding elapsed business days. It works on the same principle of using a reference Date/Time, in this case 1/8/1900 at 16:00 GMT (9 a.m. PDT), and then finding your Dates’ respective distances from that reference. The formula rounds the value it finds to the nearest hour and assumes an 8–hour, 9 a.m. – 5 p.m. work day.
You can change the eights in the formula to account for a longer or shorter work day. If you live in a different time zone or your work day doesn’t start at 9:00 a.m., change the reference time to the start of your work day in GMT. See A Note About Date/Time and Time Zones for more information.
You can Refer the same in Salesforce.com Help Documentation