Introduction
A common business problem is calculating the number of normal week days, between two dates, excluding weekends. For example, a phone repair company needs to count the business days between receiving your phone and actually fixing it, and exclude weekends in this calculation.
Background
Most of the solutions I have encountered on the web, focus on looping through the dates and checking whether it’s a normal week day or a weekend day. Although such solutions are absolutely valid, they tend to consume CPU cycles looping through every day in the period. Additionally, since the loop needs to check all days in a period given, a year period will take many times more to calculate than a month period!
Actual tests of similar code verify that the loop solution does not scale well. An example solution published on Code Project, requires on my PC, for a million iterations, 4,831 secs to calculate dates between 17/4/2016 and 30/4/2016 and 84.31 secs to calculate 17/4/2016 to 25/12/2016.
Of course, nowadays PCs are extremely powerful and the delay is barely noticeable, if you only use the function once in your program. Still, if you need to do such calculations many thousands of times, for example in a real time multi user system, such as a web site with thousands of users, you create a noticeable load.
Is there a better solution?
Solution
Yes, there is, thanks to VB.NET DateDiff function:
Public Function GetDaysBetweenDatesExcludingWeekends(ByVal dFrom As Date, ByVal dTo As Date,_
Optional ByVal DaysOff As List(Of Date) = Nothing) As Integer
Try
Dim dy As DayOfWeek
Dim _tmp As Integer = CInt(DateDiff(DateInterval.Day, dFrom, dTo))
Dim saturdays As Integer = CInt(DateDiff(DateInterval.WeekOfYear, _
dFrom, dTo, FirstDayOfWeek.Saturday))
Dim sundays As Integer = CInt(DateDiff(DateInterval.WeekOfYear, _
dFrom, dTo, FirstDayOfWeek.Sunday))
If Not DaysOff Is Nothing Then
For Each d In DaysOff
dy = d.DayOfWeek
If d > dFrom AndAlso d < dTo AndAlso dy <> DayOfWeek.Saturday _
AndAlso dy <> DayOfWeek.Sunday Then
_tmp -= 1
End If
Next
End If
Return _tmp - (saturdays + sundays)
Catch ex As Exception
handleError(ex)
Return 0
End Try
End Function
The function just calculates the number of days between the two dates provided and then just subtracts the number of Saturdays and Sundays from the total number of days. Also, if a non null List(Of Date) is provided for holidays, it will include those in the calculation.
This is possible, thanks to DateDiff function, which is unfortunately not available in C#, although I have seen some third party implementations of it in C#. Of course, one can include the Microsoft.VisualBasic namespace in a C# program. Although this is a valid solution, it's kind of stretching the limit.
Actual tests reveal that the solution proposed, after some optimization, outperforms the looping every day solution by a factor of nearly 12 times for small periods (for example 17/4/2016 to 30/4/2016). If executed a million times, GetDaysBetweenDatesExcludingWeekends("17/4/2016","30/4/2016"), takes only 365ms on my PC.
Even better, the DateDiff solution, scales amazingly well for larger periods. After applying optimization tweaks, enabling Option Strict On option in the VB.NET project, etc., the calculation of week days between 17/4/2016 and 25/12/2016, a million times, requires again only 364 ms. This is maintained even for longer periods, for example 17/4/2016 to 25/12/2028 meaning that DateDiff method essentially takes the same time, whether the time span is 20 days or 20 years. That means the DateDiff method proposed, after optimisations performs 230 times faster, than a looping every day solution, for a period of a few months!
Even an optimized looping every day solution with caching, with unrealistic assumptions, cannot perform better, than 1500 ms on my PC (17/4/2016 to 25/12/2016, a million times) and it will display increased times, for bigger time spans. That is essentially the problem with such a solution, you use CPU to process something that should be covered algorithmically and not by brute force.
And here comes the excellent solution by rlcowley, proposed in a comment, posted here. What if we incorporate the reality of week days and weekends in an algorithm? Just calculate the number of weeks, dividing the time span by 7. The integer result is multiplied by 5 to give us the working days of those weeks and finally, the remaining days, for timespans not beginning and ending on Sundays, are visited to give the final result.
The optimized routine is presented below, thanks again to rlcowley, for the original idea!
Public Function GetDaysBetweenDatesExcludingWeekendsExt(ByVal dFrom As Date, ByVal dTo As Date,
Optional ByVal DaysOff As List(Of Date) = Nothing) As Integer
Try
Dim ts As TimeSpan = dTo - dFrom
Dim weeks As Integer = ts.Days \ 7
Dim weekdays As Integer = weeks * 5
Dim dy As DayOfWeek
For i As Integer = (weeks * 7) + 1 To ts.Days
dy = dFrom.AddDays(i).DayOfWeek
If dy = DayOfWeek.Saturday AndAlso dy = DayOfWeek.Sunday Then
weekdays += 1
End If
Next
If Not DaysOff Is Nothing Then
For Each d In DaysOff
dy = d.DayOfWeek
If d > dFrom AndAlso d < dTo AndAlso dy <> DayOfWeek.Saturday _
AndAlso dy <> DayOfWeek.Sunday Then
weekdays -= 1
End If
Next
End If
Return weekdays
Catch ex As Exception
handleError(ex)
Return 0
End Try
This solution outperforms easily the DateDiff solution for time spans beginning and ending on Sundays, because it does not need to loop at all. Calculation of week days between 17/4/2016 to 25/12/2016 a million times takes only 64 ms on my PC!!! However for other timespans, it needs to loop a little for the last week, so the same calculation for the period 17/4/2016 - 30/4/2016, takes 306ms, still better than the DateDiff solution. Additionally, this solution also scales well, there seems to be little difference if the timespan is 15 days or 15 years.
The times of such calculations on my PC for a million iterations, are presented in the following table:
| Method |
Time (ms) |
DateDiff using method from 17/4/2016 to 30/4/2016 |
366 |
DateDiff using method from 17/4/2016 and 25/12/2016 |
364 |
Week calculation method from 17/4/2016 to 30/4/2016 |
306 |
DateDiff using method from 17/4/2016 and 25/12/2016 |
64 |
DateDiff with holidays using method from 17/4/2016 to 30/4/2016 |
631 |
DateDiff with holidays using method from 17/4/2016 to 25/12/2016 |
639 |
Week calculation method with holidays from 17/4/2016 to 30/4/2016 |
582 |
Week calculation method with holidays from 17/4/2016 to 25/12/2016 |
349 |
Using the Code
Just the run the project provided.
If you would like to include just one function in your project, please use the following code (handleError removed and replaced by a simple MsgBox, in order to avoid including also modErrors).
Public Function GetDaysBetweenDatesExcludingWeekends(ByVal dFrom As Date, ByVal dTo As Date,_
Optional ByVal DaysOff As List(Of Date) = Nothing) As Integer
Try
Dim dy As DayOfWeek
Dim _tmp As Integer = CInt(DateDiff(DateInterval.Day, dFrom, dTo))
Dim saturdays As Integer = CInt(DateDiff(DateInterval.WeekOfYear, dFrom, _
dTo, FirstDayOfWeek.Saturday))
Dim sundays As Integer = CInt(DateDiff(DateInterval.WeekOfYear, dFrom, _
dTo, FirstDayOfWeek.Sunday))
If Not DaysOff Is Nothing Then
For Each d In DaysOff
dy = d.DayOfWeek
If d > dFrom AndAlso d < dTo AndAlso dy <> _
DayOfWeek.Saturday AndAlso dy <> DayOfWeek.Sunday Then
_tmp -= 1
End If
Next
End If
Return _tmp - (saturdays + sundays)
Catch ex As Exception
handleError(ex)
Return 0
End Try
End Function
Public Function GetDaysBetweenDatesExcludingWeekendsExt(ByVal dFrom As Date, ByVal dTo As Date,_
Optional ByVal DaysOff As List(Of Date) = Nothing) As Integer
Try
Dim ts As TimeSpan = dTo - dFrom
Dim weeks As Integer = ts.Days \ 7
Dim weekdays As Integer = weeks * 5
Dim dy As DayOfWeek
For i As Integer = (weeks * 7) + 1 To ts.Days
dy = dFrom.AddDays(i).DayOfWeek
If dy = DayOfWeek.Saturday AndAlso dy = DayOfWeek.Sunday Then
weekdays += 1
End If
Next
If Not DaysOff Is Nothing Then
For Each d In DaysOff
dy = d.DayOfWeek
If d > dFrom AndAlso d < dTo AndAlso dy <> _
DayOfWeek.Saturday AndAlso dy <> DayOfWeek.Sunday Then
weekdays -= 1
End If
Next
End If
Return weekdays
Catch ex As Exception
handleError(ex)
Return 0
End Try
End Function
Please mention if you find this useful.
Points of Interest
- NET Framework routines are most of the times faster, than achieving the same result through code!
- A good algorithm should focus on the problem itself, not adapt some general technique.
