Fast Calculation of Week Days Between Two Dates

• Download source code - 63.1 KB

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:

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

''' Created by E. Gimissis Copyright 2016
   ''' <summary>
   ''' Calculates the number of week days between dFrom and dTo. DaysOff is a list of known holidays.
   ''' </summary>
   ''' <param name="dFrom"></param>
   ''' <param name="dTo"></param>
   ''' <returns> Integer number of week days</returns>
   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

   ''' Created by E. Gimissis, Many thanks to rlcowley for contributing the main loop
   ''' <summary>
   ''' Calculates the number of week days
   ''' between dFrom and dTo. DaysOff is a list of known holidays.
   ''' </summary>
   ''' <param name="dFrom"></param>
   ''' <param name="dTo"></param>
   ''' <returns> Integer number of week days</returns>
   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.