So someone approached me at work and wanted a better way to calculate the number of business days between two dates. I am not sure if what i came up with was better, but it was longer!
I essentially built a function that outputs a data table. The fellow that needed it hasnt quite decided if he will implement the resulting table as a SQL table on a server somewhere or if he'll wrap it in a service ( my suggestion).
The key problems were accounting for holidays and for business days that bridge years (e.g. December 2003 to January 2004). The following example was tested for a 3 year span and generates a datatable in about a half second.
so I have a function that accepts two arguements, an array of integers representing the years (e.g. 2003,2004,2005) and an array of Dates representing the dates that holidays are observed on.
| |
Public Shared Function CalendarDT(ByVal Years() As Integer, _ ByVal HolidayDates() As Date) As DataTable
|
I do no validation, but if I were to productionalize this, then I would first make sure that the values in the Years integer array were sequential and above a certain preset minimum (2001, for example).
Since this function returns a DataTable, the next step for me is to create the DataTable return object and its structure.
| |
'''create return datatable structure Dim retDT As New DataTable() retDT.Columns.Add("CalendarDate", GetType(Date)) retDT.Columns.Add("DayOfMonth", GetType(Integer)) retDT.Columns.Add("DayOfYear", GetType(Integer)) retDT.Columns.Add("PerpetualDayCount", GetType(Integer)) retDT.Columns.Add("isREOBusinessDay", GetType(Integer)) retDT.Columns.Add("REODayOfMonth", GetType(Integer)) retDT.Columns.Add("REODayOfYear", GetType(Integer)) retDT.Columns.Add("REOPerpetualDayCount", GetType(Integer))
|
This represents the information needed for the developer that approached me. The CalenderDate is an actual date. The DayOfMonth represents something like January 15th is the 15th day of the month. DayOfYear is similiar, as in February 15 is the 46th day of the year. The PerpetualDayCount is how you can find out something like the following: for a span starting 1/1/2003, 10/14/2004 is 652 days from 1/1/2003.
Now all of these calculations take every day into account. Not extremely useful yet for a process that needs to know when the 35 business day from 7/13/2004 is. So that takes us onto the next columns.
isREOBusinessDay is a column that we will populate with a 1 or 0 (could have been Boolean) for whether the date is a business day or not ('Not' included weekends and the holiday array that was entered earlier). REODayOfMonth is like the DayOfMonth listed before, but it wont count weekends and holidays. The same goes for all of the rest of the fields.
Now the next part of the fun begins. This will essentially be the creation and manipulation of arrays, which then get output to a DataTable.
| |
Dim totalDays As Integer Dim idx As Integer '''find total number of days needed for calender For idx = 0 To Years.Length - 1 totalDays += getNumberOfDays(Years(idx)) Next
|
For the number of years provided (Years array) I need to find out the total number of days. I use this function.
| |
Private Shared Function getNumberOfDays(ByVal Year As Integer) As Integer If (Year Mod 4 = 0 And Year Mod 1000 = 0 And Year Mod 400 = 0) Or _ (Year Mod 4 = 0 And Year Mod 1000 <> 0) Then Return 366 Else Return 365 End If End Function
|
Just a quick way to find out of the given year is a leap year or not.
With all of that nonsense complete we can move on. I use arrays (I like arrays). We could use collections, or if this was a bit more formal, possible structures or objects to represent the different columns of data. Maybe that will be the new version. Essentially, I am going to make an array to correspond to the
CalendarDate, isREOBusinessDay, and REOPerpetualDayCount columns of the DataTable that will be returned. Instantiating these arrays of the proper type and size is half the work. From the code above, we know the number of days total for the given span. The ForLoop starts at 1 (you know January 1st is the first day and it will be a holiday) and at each successive index makes the date object one day higher than the previous date object. The If Structure then checks to see if its not a weekend or holiday (by checking for the return value of the IndexOf method). If its not a holiday, we change the value of the isREODay array at that index to 1 (all the items were instantiated to 0 by default), we increase the totalREODays by 1 and assign that value to the REO_DayCount(this array will become our
REOPerpetualDayCount) at that index.
| |
'''build and populate array with one entry for each day in range of years passed Dim AllDates(totalDays - 1) As Date Dim isREODay(totalDays - 1) As Integer Dim totalREODays As Integer Dim REO_DayCount(totalDays - 1) As Integer AllDates(0) = New Date(Years(0), 1, 1) For idx = 1 To AllDates.Length - 1 AllDates(idx) = AllDates(idx - 1).AddDays(1) '''check to see if date is Weekend or Holiday If Not (AllDates(idx).DayOfWeek = DayOfWeek.Saturday Or _ AllDates(idx).DayOfWeek = DayOfWeek.Sunday Or _ [Array].IndexOf(holidayDates, AllDates(idx)) >= 0) Then isREODay(idx) = 1 totalReoDays += 1 REO_DayCount(idx) = totalReoDays End If Next
|
Now we are going to calculate
REODayOfMonth and REODayOfYear values. We will accomplish this be looping thru our now-populated AllDates array. The first if structure is how we determine if we have crossed a month boundary or a year boundary since this code works for a span of years. The second If structure allows us to determine if the day in question is a Business Day or not. | |
'Calculate REO Business day of month for particular date Dim REOBusinessDaysInMonth(totalDays - 1) As Integer Dim REOBusinessDaysInYear(totalDays - 1) As Integer Dim month As Integer Dim day As Integer Dim dayOfYear As Integer day = 1 month = 1 dayOfYear = 1 For idx = 0 To AllDates.Length - 1 If AllDates(idx).Month <> month Then If month < 12 Then month += 1 Else month = 1 dayOfYear = 1 End If day = 1 End If If isREODay(idx) = 1 Then REOBusinessDaysInMonth(idx) = day REOBusinessDaysInYear(idx) = dayOfYear day += 1 dayOfYear += 1 End If Next
|
With this done we are almost finished. All that's needed now is to place the data in the DataTable to be returned. Looping again thru the AllDates array, we can assign the values to the different fields of a DataRow from our DataTable. Notice we didnt need an array for the
PerpetualDayCount, DayOfMonth, or DayOfYear as we could derive them from the index of the array or the intrinsic properties of the date object stored in the AllDates array. | |
'''add values to datatable For idx = 0 To AllDates.Length - 1 Dim r As DataRow r = retDT.NewRow() r("CalendarDate") = AllDates(idx) r("isREOBusinessDay") = isREODay(idx) r("DayOfMonth") = AllDates(idx).Day r("DayOfYear") = AllDates(idx).DayOfYear r("PerpetualDayCount") = idx + 1 r("REOPerpetualDayCount") = REO_DayCount(idx) r("REODayOfMonth") = REOBusinessDaysInMonth(idx) r("REODayOfYear") = REOBusinessDaysInYear(idx) retDT.Rows.Add(r) Next '''return datatable to caller Return retDT End Function
|
Next step would be to wrap this in an object or service. To complete this, you would only need some functions that kept this DataTable as a private member of some object, like possibly:
| |
Public Function IsBusinessDay(ByVal CDate As Date) As BooleanPublic Shared Function BusinessDateFromDate(ByVal StartDate As Date, _ ByVal NumberOfDays As Integer) As DatePublic Shared Function BusinessDaysBetween Dates(ByVal StartDate As Date, _ ByVal EndDate As Date) As Integer
|
When I find the time I want to rewrite this completly as a DataTable/ADO.NET solution without all the arrays.
The Universe has no center and I am it.
The superior man is modest in his speech, but exceeds in his actions.