CODEWITHSUNDEEP
vb.netssrs-2008
James
James

Reputation: 506

Count the number of weekdays between two dates in visual basic

I'm a SQL guy, but I need a function to calculate the number of weekdays between two dates in VB.NET. I don't need to worry about holidays. My attempts unfortunately have been futile. Much appreciated

This will go in custom code in Reporting Service 2008 R2.

Upvotes: 2

Views: 21047

Answers (6)

Mas Harjo
Mas Harjo

Reputation: 73

'This is my version vb.net 2013 and its works

Private Sub enddate_ValueChanged(sender As Object, e As EventArgs) Handles enddate.ValueChanged
        Dim countsun As Integer = 0
        Dim countsat As Integer = 0
        Dim nonholiday As Integer = 0
        Dim totalDays = (enddate.Value - startdate.Value).Days
        For i = 0 To totalDays
            Dim Weekday As DayOfWeek = startdate.Value.Date.AddDays(i).DayOfWeek
            If Weekday = DayOfWeek.Saturday Then
                countsat += 1
            End If
            If Weekday = DayOfWeek.Sunday Then
                countsun += 1
            End If
            If Weekday <> DayOfWeek.Saturday AndAlso Weekday <> DayOfWeek.Sunday Then
                nonholiday += 1
            End If
        Next
        lblSumHeadCount.Text = nonholiday & " Day(s)"
    End Sub

'Thanks Gabe

Upvotes: 1

Felix
Felix

Reputation: 99

I think this may help

Public Shared Function WeekEndsBetweenDates(ByVal StartDate As Date, ByVal EndDate As Date) As Integer
    Dim wkday, wkend As Integer
    For i As Integer = 0 To DateDiff(DateInterval.Day, StartDate, EndDate)

        If DateAdd(DateInterval.Day, i, StartDate).DayOfWeek = DayOfWeek.Saturday Or DateAdd(DateInterval.Day, i, StartDate).DayOfWeek = DayOfWeek.Sunday Then
            wkend += 1
        Else
            wkday += 1
        End If
    Next
    Return wkend
End Function

Here, i returned "wkend" which is the weekend part of the variables in the function. Alternatively, you can change the returned value to "wkday" which is the number of weekdays between the selected dates. I hope this helps. It works for me though

Upvotes: 0

aila84
aila84

Reputation: 1

I have an expression that will calculate amount of specific day of week between two dates. So if you add amount on Mondays, Tuesdays ... Fridays you will get the amount of week days.

=SUM(int((DatePart("d",Fields!dteEndDateTime.Value) - Weekday(DateAdd("d",1-**[DayofWeek]**,Fields!dteEndDateTime.Value)) - DatePart("d",Fields!dteStartDateTime.Value) + 8)/7))

[DayofWeek] is an integer representing a day: 1 - Sunday; 2 - Monday

So an expression that will calculate amount of Mondays between dteStartDateTime and dteEndDateTime is:

=SUM(int((DatePart("d",Fields!dteEndDateTime.Value) - Weekday(DateAdd("d",1-2,Fields!dteEndDateTime.Value)) - DatePart("d",Fields!dteStartDateTime.Value) + 8)/7))

Upvotes: 0

Mauricio Morales
Mauricio Morales

Reputation: 1018

This may help someone else looking for this. This function doesn't loop day by day.

    public static double WorkDays(DateTime start, DateTime end)
    {
        var delta = end.AddDays(1) - start;
        var fullWeeks = (int)(delta.TotalDays / 7);
        var workDays = fullWeeks * 5;

        var partialWeekDays = delta.TotalDays % 7;
        if (partialWeekDays > 0)
        {
            var startWeekday = start.DayOfWeek;
            var endWeekday = end.DayOfWeek;

            if (startWeekday == DayOfWeek.Sunday || startWeekday == DayOfWeek.Saturday)
                partialWeekDays--;
            if (startWeekday > DayOfWeek.Sunday && startWeekday < DayOfWeek.Saturday &&
                startWeekday > endWeekday)
                partialWeekDays--;
            if ((endWeekday == DayOfWeek.Sunday || endWeekday == DayOfWeek.Saturday) && endWeekday != startWeekday)
                partialWeekDays--;
            if (endWeekday > DayOfWeek.Sunday && endWeekday < DayOfWeek.Saturday &&
                startWeekday > endWeekday)
                partialWeekDays--;
        }

        return workDays + partialWeekDays;
    }

Upvotes: 0

MRAB
MRAB

Reputation: 20644

You don't need to check whether every single day between those dates is a weekday.

If there are n days, then there are int(n / 7) complete weeks, each containing 5 weekdays, so that's 5 * int(n / 7) weekdays.

You then need to check the days of the remaining partial week (0..6 days).

Upvotes: 5

Gabe
Gabe

Reputation: 6045

Try this. I modified an existing function that I've been using. This will work in SSRS 2008. Note, that you can also write your code in C#, if you're more comfortable with that, and after deploying it, just reference the assembly from the report.1

public Shared Function Weekdays(ByRef startDate As Date,  ByRef endDate As Date   ) As integer
    dim numWeekdays as Integer
    dim totalDays as Integer
    dim WeekendDays as Integer
    numWeekdays = 0
    WeekendDays = 0

    totalDays = DateDiff(DateInterval.Day, startDate , endDate ) + 1

    for i as integer = 1 to totalDays

        if DatePart(dateinterval.weekday,startDate) = 1 then
            WeekendDays = WeekendDays + 1
        end if
        if DatePart(dateinterval.weekday, startDate) = 7 then
            WeekendDays = WeekendDays + 1
        end if
            startDate = DateAdd("d", 1, startDate)
    next

    numWeekdays  = totalDays - WeekendDays 

    return numWeekdays  
End Function

Upvotes: 10

Related Questions