How many days between dates without weekends

[jadams0173]

I'm trying to take the weekends out from a date range. What I'm doing is letting the user look forward or backward any number days, months or years and tell them how many days there are with and without weekends.

It's part of a planning tool that I've been trying to build so we can get a ball park of how much money we do pay and wouldn't pay in OT if we could do a better job of planning.

Is there a mathmatical way to figure out how many weekend days there are and exclude them regardless of what the day is?
I'd perfer not to use a loop if at all possible.

So far I have:

Dim TotalDays As Double
'this should be the days with the weekends
TotalDays = CInt(txtDays.Text)
                    TotalDays += CInt(txtMonths.Text) * 30.4 '365/12 assume 30.4 days for month
                    TotalDays += CInt(txtYears.Text) * 365 '365 days per year

'this is the new date based on the user input
TodaysDate = TodaysDate.AddDays(CDbl(txtDays.Text))
                    TodaysDate = TodaysDate.AddMonths(CInt(txtMonths.Text))
                    TodaysDate = TodaysDate.AddYears(CInt(txtYears.Text))

Public Class Form1
    Dim dte As Date
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        dte = Now
        lblTodaysDate.Text = dte.ToString("D")
        
End Sub

 

[Andrzejek]

It is not VB.NET, it is a code from VB 6, but it should give you and idea of calculating the WorkDays, which is what you are after:

Public Function Work_Days(strBegDate As String, strEndDate As String) As Long[green]
' Note that this function does not account for holidays.
[/green]
Dim intWeekend As Integer
Dim varDate
Dim i As Integer

Work_Days = DateDiff("d", strBegDate, strEndDate)

varDate = strBegDate
For i = 1 To Work_Days
    If Format(varDate, "ddd") = "Sun" Or _
      Format(varDate, "ddd") = "Sat" Then
       intWeekend = intWeekend + 1
    End If
    varDate = DateAdd("d", 1, varDate)
Next i

Work_Days = Work_Days - intWeekend + 1

End Function

Have fun.

---- Andy

 

[jadams0173]

Thanks andy. I'll give that a shot. As an after thought, could it be done without a loop?

 

[Andrzejek]

Probably...

The problem is, if you have 4 days span, this could include a weekend (from Friday to Monday, 2 working days), or not (from Monday to Thursday, 4 working days). If you have a 10 days span, it may include just one weekend (8 working days), or it may include 2 weekends (6 working days). Do you see the problem?

You may get away with loop if you check either start day or end day and figure out the logic.

Have fun.

---- Andy

 

[Foada]

Like Andrzejek is saying you could do something like this.

    Private Function WorkDays(ByVal strStartDate As String, ByVal strEndDate As String)
        Dim iWeeks As Long
        Dim iStartDay As Long
        Dim iEndDay As Long
        Dim iDays As Long
        Dim iDifference As Long
        Dim iX As Long
        Dim iBridgeWeekend As Long
        
        iWeeks = DateDiff("w", strStartDate, strEndDate)
        iStartDay = Weekday(strStartDate, vbMonday)
        iEndDay = Weekday(strEndDate, vbMonday)

        If iStartDay > iEndDay Then
            iBridgeWeekend = 7
        End If
        iDays = (iWeeks * 7) - (iWeeks * 2)
        For iX = iStartDay To (iBridgeWeekend + iEndDay)
            If iX <> 6 And iX <> 7 Then
                iDifference = iDifference + 1
            End If
        Next iX
        WorkDays = iDays + iDifference
    End Function

It is basically the same code it just gets the weeks and then determines the relative position of the start and end days regarding bridging a weekend. You can get rid of the iDays and iDifference variables and just use the function name variable as well. I just left them there for clarity. It still uses a loop but the most iterations it would make is 7. I think this tests ok. I found an issue with the other code given dates of 5-16-09 and 6-7-09 produces a result of 16 and I believe it should be 15 FYI. Good Luck.

If you choose to battle wits with the witless be prepared to lose.

 

[jadams0173]

Thanks for the replies. Andy, I had thought about the dynamic of starting mid week on different days. That has been my stumbling block on a way to do it without a loop. I have decided to just use the loop and be happy. I may when time permits try to figure a "loopless" solution.

 

[Foada]

jadams0173 the code post that I put up handles the mid week starting without looping through the dates between the start and end. It only loops through the partial week to account for the mid week start dates. It does not account for Holidays but that too would be easy to implement.

If you choose to battle wits with the witless be prepared to lose.

 

[DaveInIowa]

Here's my "loopless" attempt at a solution. It does cheat however by using a Select/Case to cover all the various from/to day-of-week combinations.

    Private Function WorkDays(ByVal fromDate As Date, ByVal toDate As Date) As Integer
        Dim result As Integer

        Dim grossDays As Integer = DateDiff(DateInterval.Day, fromDate, toDate)
        Dim fullWeeks As Integer = grossDays \ 7
        result = fullWeeks * 5

        ' The following Select/Case statement handles all from/to day-of-week combinations to add
        ' the necessary number of days to the result field.
        ' Note that same day-of-week combinations (Mon>Mon, etc.) are not needed as they will have
        ' been covered by the fullWeeks calculation above.
        ' Also, the 3 special cases of Fri>Sat, Fri>Sun and Sat>Sun are not needed as they result
        ' in no additional days being added.

        Select Case (fromDate.DayOfWeek * 10 + toDate.DayOfWeek)
            'Sun>Mon, Mon>Tue, Tue>Wed, Wed>Thu, Thu>Fri, Thu>Sat, Thu>Sun, Fri>Mon, Sat>Mon
            Case 1, 12, 23, 34, 45, 46, 40, 51, 61 : result += 1

                'Sun>Tue, Mon>Wed, Tue>Thu, Wed>Fri, Wed>Sat, Wed>Sun, Thu>Mon, Fri>Tue, Sat>Tue
            Case 2, 13, 24, 35, 36, 37, 41, 52, 62 : result += 2

                'Sun>Wed, Mon>Thu, Tue>Fri, Tue>Sat, Tue>Sun, Wed>Mon, Thu>Tue, Fri>Wed, Sat>Wed
            Case 3, 14, 25, 26, 20, 31, 42, 53, 63 : result += 3

                'Sun>Thu, Mon>Fri, Mon>Sat, Mon>Sun, Tue>Mon, Wed>Tue, Thu>Wed, Fri>Thu, Sat>Fri
            Case 4, 15, 16, 10, 21, 32, 43, 54, 64 : result += 4

                'Sun>Fri, Sun>Sat, Sat>Fri
            Case 5, 6, 65 : result += 5
        End Select

        Return result
    End Function

 

[gmmastros]

Take a look at vongrunt's post in this thread.

thread183-1416255

It's a SQL Server solution, but it shouldn't be too difficult to translate it to VB.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[softhemc]

There is no need for a loop at all (or the specific handling each scenario with a Select Case statement).

The following uses two DateTimePickers to input the dates and a Label to display the result.

I think I've put sufficient comments in to explain the logic.

	Private Function WeekdaysOnly(ByVal Date1 As Date, ByVal Date2 As Date) As Integer

		'Get the dates and, if necessary turn them around so that StartDate comes before EndDate
		Dim StartDate As Date = If(Date1.Date <= Date2.Date, Date1, Date2)
		Dim EndDate As Date = If(Date1.Date <= Date2.Date, Date2, Date1)

		Dim TotalDays As Integer = CInt(EndDate.Subtract(StartDate).TotalDays)

		'Sunday = 0, Saturday = 6
		Dim StartDOW As Integer = CInt(StartDate.DayOfWeek)
		Dim EndDOW As Integer = CInt(EndDate.DayOfWeek)

		'Adjust the DayOfWeek so that Sunday = 6 and Saturday = 5, Weekdays are then 0 to 4
		StartDOW -= 1 + If(StartDOW = 0, -7, 0)
		EndDOW -= 1 + If(EndDOW = 0, -7, 0)

		Dim Result As Integer = 0

		'To handle the number of weekdays at the start, subtract the modified DOW from 6
		If StartDOW < 5 Then
			Result = 6 - (StartDOW + 1)
		End If

		'No need for any special handling to get the number of weekdays at the end
		If EndDOW < 5 Then
			Result += (EndDOW + 1)
		End If

		'Adjust the TotalDays so that only "whole weeks" remain
		TotalDays -= Result

		'Use Integer divide "\" to get the number of whole weeks and multiply this value by 5 to get the number of Weekdays
		Return Result + ((TotalDays \ 7) * 5)

	End Function


	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

		Label1.Text = WeekdaysOnly(DTP1.Value, DTP2.Value).ToString

	End Sub

As an aside, I've used VB 2008. If you are using an earlier version, you will need to change the If(condition, trueresult, falseresult) function and use IIf instead. If you have to use IIf and have Option Strict ON, you will need to wrap the IIf in a CInt to return the required Integers.

 

[jwigh]

I have a set of functions to do calculations on date, holidays and date spanns.

The function WrkDate test if a given date is a Working day or (swedish) public holiday/weekend.

The function Endday takes a startdate and a spann to calculate the Endday

Public Function WrkDay(ByVal InDate) As Boolean 'Result False for Saturday, Sunday and holidays True for WrkDay
        Dim Year, A, B, C, D, E, F, m, n As Integer
        Dim EasterDay As Date
        Select DatePart("w", InDate, vbMonday, vbFirstFourDays)
            Case 6, 7
                WrkDay = False ' InDate is either Saturday or Sunday
                Exit Function
            Case Else ' Wrkday is M-Fr

                If DatePart("m", InDate, vbMonday, vbFirstFourDays) = 1 And (DatePart("d", InDate, vbMonday, vbFirstFourDays) = 1 Or DatePart("d", InDate, vbMonday, vbFirstFourDays) = 6) Then
                    WrkDay = False 'InDate is New Years Day or Epiphany 
                    Exit Function
                End If

                If DatePart("m", InDate, vbMonday, vbFirstFourDays) = 5 And DatePart("d", InDate, vbMonday, vbFirstFourDays) = 1 Then
                    WrkDay = False 'InDate is First of May 
                    Exit Function
                End If

                If DatePart("m", InDate, vbMonday, vbFirstFourDays) = 12 And (DatePart("d", InDate, vbMonday, vbFirstFourDays) = 24 Or DatePart("d", InDate, vbMonday, vbFirstFourDays) = 25 Or DatePart("d", InDate, vbMonday, vbFirstFourDays) = 26 Or DatePart("d", InDate, vbMonday, vbFirstFourDays) = 31) Then
                    WrkDay = False 'InDate is Christmas Eve or Day, Boxingday or New Years Eve
                    Exit Function
                End If
                If DatePart("m", InDate, vbMonday, vbFirstFourDays) = 6 And (DatePart("d", InDate, vbMonday, vbFirstFourDays) = 6) Then
                    WrkDay = False 'InDate is National Day June 6
                    Exit Function
                End If

                If DatePart("m", InDate, vbMonday, vbFirstFourDays) = 6 And (DatePart("d", InDate, vbMonday, vbFirstFourDays) >= 19 And DatePart("d", InDate, vbMonday, vbFirstFourDays) <= 25) And DatePart("w", InDate, vbMonday, vbFirstFourDays) = 5 Then
                    WrkDay = False 'InDate is Midsummer Eve
                    Exit Function
                End If



                m = 24 'constant valid until 2199
                N = 5 'constant valid until 2099
                A = Year Mod 19
                B = Year Mod 4
                C = Year Mod 7
                D = (19 * A + m) Mod 30
                E = (2 * B + 4 * C + 6 * D + N) Mod 7
                F = 22 + D + E
                If F = 57 Or (F = 56 And E = 6 And A > 10) Then F = F - 7

                If F <= 31 Then
                    EasterDay = CDate(Year & "-" & "03-" & F)

                Else
                    EasterDay = CDate(Year & "-" & "04-" & D + E - 9)

                End If
                'Find Good Friday Easter Monday Ascension Day
                If InDate = EasterDay.AddDays(-2) Or InDate = EasterDay.AddDays(1) Or InDate = EasterDay.AddDays(39) Then
                    WrkDay = False
                    Exit Function
                End If
        End Select
        WrkDay = True 'InDate is a WrkDay
    End Function


    Public Function Endday(ByVal StartDate As Date, ByVal Spann As Integer) As Date

        Do
            If WrkDay(StartDate) = False Then
                Spann = Spann + 1
            End If
            StartDate = StartDate.AddDays(1)
            Spann = Spann - 1
        Loop Until Spann = 0
        Enddate = InDate
    End Function

 

[SBendBuckeye]

Dim d1 As New Date(Now.Ticks), d2 As New Date(2000, 12, 25)
Dim weekDays As Integer = Convert.ToInt32(New TimeSpan(d1.Ticks - d2.Ticks).Days / 7) * 5
If d1.DayOfWeek = DayOfWeek.Saturday OrElse d1.DayOfWeek = DayOfWeek.Sunday Then weekDays -= 1
If d2.DayOfWeek = DayOfWeek.Saturday OrElse d2.DayOfWeek = DayOfWeek.Sunday Then weekDays -= 1