[RESOLVED] how to calculate the difference between two dates ignoring weekends?

[vblover2008]

Hi

I need to calculate the difference between two days excluding weekends.

this code counts the difference between two days.

 Public Class Form1
    Dim date1 As Date
    Dim date2 As Date
    Dim difference As Integer
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        date1 = #3/5/2009#
        date2 = #1/6/2009#
        Dim daysTimeSpan As TimeSpan = date1.Date.Subtract(date2.Date)
        difference = CInt(daysTimeSpan.Days)
        MsgBox(difference)
        

    End Sub
End Class 

thanks

[keystone_paul]

Good question - does this need to work worldwide or is it safe to assume that the weekend is Saturday and Sunday?

If it needs to work internationally (say the UK or US and the Middles-East where the weekend falls on different days) I cant think of anything other than looping through from startdate to enddate and increment a counter if WeekDay(thisDay) = True - assuming thisDay being your loop counter. Not great for big timespans but OK for short ones?

If your code is only going to apply in one country you could try and work out the number of complete weeks in the span, working from the closest Monday (assuming Monday to be the start of the week) to the end of the span divided by 7. For each full week subtract two days from the total you've already calculated in your example), and then remove any padding you added by starting at the beginning of the week and add any days that spill over into the last week.

[vblover2008]

I tried it, the number of days are increasing I think lol
at first Diff was = 58, after calling the function it went to 76.
weekend days are Saturday and Sunday.

is this right?

Public Class Form2
    Dim StartDate As Date
    Dim EndDate As Date
    Dim Diff As Long
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        StartDate = #3/5/2009#
        EndDate = #1/6/2009#
        Dim daysTimeSpan As TimeSpan = StartDate.Date.Subtract(EndDate.Date)
        Diff = CInt(daysTimeSpan.Days)
        MsgBox(Diff)
        MsgBox(NoWeekends(StartDate, Diff))

    End Sub
    Public Function NoWeekends(ByVal StartDate As Date, ByVal Diff As Long) As Long
       
        Dim WorkDays As Long
        'Use Saturday as the start day of the week
        If Weekday(StartDate, vbSaturday) = 1 Then
            WorkDays = Diff + 2
        ElseIf Weekday(StartDate, vbSaturday) = 2 Then
            WorkDays = Diff + 1
        Else
            WorkDays = Diff
        End If
        
        WorkDays = WorkDays + Int((WorkDays + Weekday(StartDate, vbSaturday)) / 7) * 2
        NoWeekends = WorkDays
    End Function
End Class 

[keystone_paul]

Sorry - a flaw in my logic (you don't need to adjust the difference by the offset days)- try this :

        date1 = #4/28/2009#
        date2 = #5/15/2009#

        Dim daysTimeSpan As TimeSpan = date2.Date.Subtract(date1.Date)
        'get difference in terms of all days
        difference = CInt(daysTimeSpan.Days)

        'get monday of start week
        Dim x As Integer = date1.DayOfWeek - 1
        Dim startDate As Date = date1.AddDays(-x)

        'get monday of last week
        Dim y = date2.DayOfWeek - 1
        Dim EndDate As Date = date2.AddDays(-y)

        'calculate the number of weekends falling between the two adjusted dates
        Dim Weekends As Integer = (DateDiff(DateInterval.DayOfYear, startDate, EndDate)) / 7

        'remove 2 days per weekend
        difference = difference - 2 * Weekends

[vblover2008]

Thank you for your help it works fine now!