I recently received a request for assistance with my JavaScript date extensions, DP_DateExtensions. In part it says:
I need to simulate the MS Excel networkdays() function. Excel would count 10th Jan 2013 midnight to 15th Jan 2013 midnight as 4 working days (6 days in total) but DP_DateExtension.js does not count the 10th so outputs 3 working days (5 total).
The Excel NetworkDays() function returns “business days” (removing Saturday and Sunday and optionally holidays from the calculation). In this case passing the function January 10th (a Thursday) to January 15 (a Tuesday) results in “4”. This makes it clear that Excel is doing this calculation inclusively by date and obviously not from “midnight to midnight”.
If you define midnight as 00:00:00 (the first instant of a given day, as JavaScript does) then the clear answer should be “3” – counting Thursday, Friday and Monday but not Tuesday (which is only represented by a split-millisecond). This is how the DP_DateExtensions diff() method calculates the value when using “businessdays”. For example this will return “3”:
CurDate1 = new Date(2013, 0, 10, 0, 0, 0); CurDate2 = new Date(2013, 0, 15, 0, 0, 0); alert(CurDate1.diff(CurDate2, "businessdays"));
Excel, on the other hand, is includs Tuesday and therefore clearly not calculating from midnight to midnight. (Both eliminate Saturday and Sunday from consideration.) Instead Excel seems to be considering the days in question as an inclusive set of integers. As weird as I may find this (and I do, indeed, find it very weird) it’s clearly something that anybody used to Excel will expect. So, how do we get the same behavior in DP_DateExtensions?
The simplest way, I think, is to leverage the floor() and ceil() methods. We floor() the earlier date and we ceil() the later date to ensure that both entire days are counted. In essence, using our example, this calculates the difference between midnight January 10 and midnight January 16th (the first instant after our end date). This would look like this:
CurDate1 = new Date(2013, 0, 10, 0, 0, 0).floor("businessdays"); CurDate2 = new Date(2013, 0, 15, 0, 0, 0).ceil("businessdays"); alert(CurDate1.diff(CurDate2, "businessdays"));
… and this would return “4” just like good old Excel. In fact I think it’s a very good bet that Excel is doing something very much like this as anybody would tell you that 15 minus 10 is “5”, not “6” (or “3”, not “4” when the weekend is removed).
In the end this is basically a definitional difference easily addressed. It also reminds us that working with dates is a huge, continual pain in the ass.
Is there any way using this date diff function to not only exclude weekend days, but also additional days, perhaps provided as an array?
I assume that you mean dates, not days? As in a list of dates (like holidays) not days (like Mondays)? It doesn’t really matter, actually, as both are pretty easy, but neither is supported directly in the library (yet, at least, it’s not a bad idea).
You can pull the framework of the code directly from the “Diff()” method in the library. The basic code is:
// First get the number of days between the dates
var IntDiff = Date1.diff(Date2, “Days”, CompareMethod, NormalizeDST);
// Count through the days and remove the Saturdays and Sundays
while ( Date1.getTime() < Date2.getTime() ) { if ( Date1.getDay() == 0 || Date1.getDay() == 6 ) { --IntDiff; }; Date1 = Date1.add(1, "days"); }; Diff = IntDiff; So: +) That first line gets total number of days as a start. +) Then we loop through the days (the "while" and the date add). +) For each day we test it - in this case to see if it's a Saturday or a Sunday, but the test can be anything you like, really. +) If there's a match, you subtract one from the total. +) Finally, return the final number. You can easily add another day, like Monday or whatever to this as is. Or you can change the test to check against an array or whatever. Just make sure that you do a single, combined test (otherwise you'll risk double-counting). It is a a good idea, tho' - I'll probably add it when I get a few minutes.
You are correct I did indeed mean a list of dates. If you can add the functionality it would be much appreciated.
Thanks
Lewis
Ok having read your comment a couple of times it all makes sense – I see that I have to add in an additional test in the IF test
if ( Date1.getDay() == 0 || Date1.getDay() == 6 )
to check to see if the date is in an array of dates, but I can’t think of a short way to loop through the date values in array that will fit nicely in the IF test.
Any suggestions?
You can just loop over the array – it’s not “elegant” or “fast” but one of the nice things about JavaScript on the client is that you can often be wasteful without impacting things too much. Assuming that you’re talking relatively small numbers (say a dozen holidays and durations of a week or two) your processing time will still be more than fast enough.
Unfortunately I’m not in a position to test this right now, but something like this should do the trick:
// Assumes “Holidays” is an array of Date objects available to the function
case “businessdays”:
// Set Date2 to the end of a non-business day
if ( Date2.getDay() == 0 || Date2.getDay() == 6 ) {
Date2.ceil(DatePart, true);
};
// First get the number of days between the dates
var IntDiff = Date1.diff(Date2, “Days”, CompareMethod, NormalizeDST);
// Count through the days and remove the Saturdays and Sundays
while ( Date1.getTime() < Date2.getTime() ) { if ( Date1.getDay() == 0 || Date1.getDay() == 6 ) { --IntDiff; } else if { for (var Cnt = 0; Cnt < Holidays.length; Cnt++) { if ( Holidays[Cnt].compare(Date1, "Day") ) { --IntDiff; }; }; }; Date1 = Date1.add(1, "days"); }; Diff = IntDiff; break; So, basically, it first does the "Sunday/Saturday" check then, if it's a weekday, it loops over the "Holiday" array (full of date objects) and does a "compare" (with a precision of "days") against each date. If it finds a match, it subtracts from the simple result and continues. There's likely ways to optimize that - and a hash map/table (too big a topic for a comment) would likely be faster if you're going to reuse the array multiple times - but this should get the job done until I can dig into more completely.