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”.
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.