In VB, calculating the 5th working day of every month

Question

In VB, what coding could I use to calculate the 5th working day of every month? And if the 5th day is a holiday to go up one day.

Answer 1

You would need a list of holiday dates with which to compare. You would need to build and store that list separately. You did not say what variant of VB (VBA? VB.NET?) but in VB.NET you could do:

Dim datevalue As DateTime = New DateTime(DateTime.Year, DateTime.Month, 1)
Dim dayIsFound As Boolean = False
Dim workDays As Integer

workDays = 1
While Not dayIsFound
    If ( dateValue.DayOfWeek <> DayOfWeek.Saturday _ 
        And dateValue.DayOfWeek <> DayOfWeek.Sunday _ 
        And Not HolidayList.Contains( dateValue )  _ 

        workDays = workDays + 1
    End If

    If  index >= 5 Then
        dayIsFound = True
    Else
        dateValue = dateValue.AddDays(1)
    End If
End While

Technically, it is possible to build an algorithm that determines the major holidays based on Federal guidelines (in the US) but it is complicated and may not conform to the holidays of the company to whom you are building this component.

Answer 2

The problem has 2 main components - first you need a list of public holidays, and second you need to count days of the week which are working days in your locale.

You can hard code your holidays for a quick solution, and if you want this code to keep working without modification you will need to Google for some algorithms / functions to calculate your holidays. Here is a link to some sample calculation functions for holidays, including Easter. http://www.cpearson.com/EXCEL/holidays.htm

Your main function can use the VB functions Weekday and WeekdayName (if needed) in conjunction with your list of public holidays to count all days that fall on a normal working day which isn't in your list of public holidays.

Answer 3

Use Weekday function for Saturday and Sunday. Holidays are dependent on the location and country and you need to implement your own function for that.

Answer 4

You need to count each valid working day until you get to the fifth one. E.g.

index = 0
Do
  If dateValue.DayOfWeek <> DayOfWeek.Saturday _ 
      AndAlso dateValue.DayOfWeek <> DayOfWeek.Sunday _ 
      AndAlso Not HolidayList.Contains( dateValue ) Then

     index = index + 1
     If index >= 5 Then _
        Exit Do
  End If
  dateValue = dateValue.AddDays(1)
Loop