Since I've been getting questions on how to extend my simple date range validation code to support business days, I thought I'd write another article to explain one way of accomplishing this using minimal code and CPU cycles. For those coming from an Excel background, this is similar to the NETWORKDAYS() function that comes with the Analysis ToolPak (InfoPath 2003 would be *THAT* much more powerful if it supports only half of Excel's built-in functions :-)).
First, I suggest you read my earlier post, Date range validation using less code. Calculating total days between two dates is a prerequisite to calculating just business days. Continuing on with the same form, we'll add a new hidden field called diffb; this field will hold the number of business days.
When I first went about trying to figure out how to code this, the first thought that went to my head was to brute force it by looping through each day from the start date till the end date, determining what day of the week it was, and incrementing the business day counter only when it's Mon-Fri (days 1-5 in JavaScript speak, with 0 being Sunday). However, I generally try to avoid using big loops in my code, so I had to rethink my solution.
In the end, I settled on a solution that used a loop with a maximum six iterations. Basically, we figure out how many whole weeks + remaining days comprise the given date range. There are always five business days in a whole week (unless you want to get fancy and add an array for holidays), so we multiply the number of weeks by 5 to get the subtotal for the number of business days; then we loop through the remaining days to add just the business days to get the grand total number of business days. Finally, we output this to the diffb field to set up our data validations.
Here's the complete code with liberal commenting:
var nDateStart, nDateEnd;
var sDateStart, sDateEnd;
var nDiff, nDiffB;
var oDiff = XDocument.DOM.selectSingleNode('my:myFields/my:diff');
var oDiffB = XDocument.DOM.selectSingleNode('my:myFields/my:diffb');
function msoxd_my_start_date::OnAfterChange(eventObj) {
// Normalize date format (yyyy-mm-dd -> mm-dd-yyyy)
sDateStart = eventObj.Site.text.replace(/(\d{4})-(.+)/,'$2-$1');
if (sDateStart) {
// Get start date serial number
nDateStart = Date.parse(sDateStart);
if (nDateEnd) {
// Calculate difference (86400000 milliseconds = 1 day)
nDiff = (nDateStart) ? (nDateEnd-nDateStart)/86400000 : '';
// How many complete weeks between start and end dates
// Multiply by 5 to get the number of business days
nDiffB = (nDiff) ? Math.floor(nDiff/7)*5 : '';
// Use % operator to get the remaining days
// Loop through remaining days to add up business days
for (var i=1, n; i<nDiff%7+1; i++) {
n = new Date(Date.parse(sDateStart)+86400000*i).getDay();
if (n>0 && n<6) nDiffB++;
}
oDiff.text = nDiff;
oDiffB.text = nDiffB;
}
}
}
function msoxd_my_end_date::OnAfterChange(eventObj) {
// Normalize date format (yyyy-mm-dd -> mm-dd-yyyy)
var sDateEnd = eventObj.Site.text.replace(/(\d{4})-(.+)/,'$2-$1');
if (sDateEnd) {
// Get end date serial number
nDateEnd = Date.parse(sDateEnd);
if (nDateStart) {
// Calculate difference (86400000 milliseconds = 1 day)
nDiff = (nDateEnd) ? (nDateEnd-nDateStart)/86400000 : '';
// How many complete weeks between start and end dates
// Multiply by 5 to get the number of business days
nDiffB = (nDiff) ? Math.floor(nDiff/7)*5 : '';
// Use % operator to get the remaining days
// Loop through remaining days to add up business days
for (var i=1, n; i<nDiff%7+1; i++) {
n = new Date(Date.parse(sDateStart)+86400000*i).getDay();
if (n>0 && n<6) nDiffB++;
}
oDiff.text = nDiff;
oDiffB.text = nDiffB;
}
}
}
Cheers,
Tom