In VB, calculating the 5th working day of every month
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开发者_C百科 one day.
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.
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.
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.
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
精彩评论