No, I certainly can’t help you get dates or have successful dates. But I can offer a couple of functions that might help you work with dates in your code. I suck at dates (all types). Every time I want to calculate dates, I need to print a calendar from Outlook and count the days. It’s pretty ironic that one of my previous projects was all about schedules. And one of my current projects deals with delivery schedules as well. So I can’t escape it.
I always would get frustrated because every month was different. Every month started on a different day and had a different number of days. It felt impossible to get all the different combinations. But recently, I had a moment of clarity and realized some basic facts about a month. Things like:
- No month has less than 28 days
- This guarantees every month will have 4 weeks
- This guarantees there are no less than 4 and no more than 5 of every weekday in a month
- The only weekdays that will have 5 occurrences will be the days in excess of 28. These days can be accounted for at the beginning or end of the month – it doesn’t matter.
- By extension, there are a minimum of 20 workdays in a month (Mon-Fri)
- And, any additional workdays will be those in excess of 28 that are between Monday and Friday
Earlier attempts to figure out the number of workdays in a month resulted in a brute force loop that would run through every day from 1 to 31 and if the DayOfWeek was Mon-Fri, increment a counter. Now, with these new guidelines, I can start at 20 and only deal with 0-3 excess days. Like with this function:
Shared Function WorkdaysInMonth(ByVal d As Date) As Integer Dim daysInMonth As Integer Dim extraWeekDays As Integer daysInMonth = New Date(d.Year, d.Month, 1).AddMonths(1).AddDays(-1).Day For i As Integer = 1 To daysInMonth - 28 Select Case New Date(d.Year, d.Month, i).DayOfWeek Case DayOfWeek.Monday, DayOfWeek.Tuesday, _ DayOfWeek.Wednesday, DayOfWeek.Thursday, DayOfWeek.Friday extraWeekDays += 1 End Select Next Return 20 + extraWeekDays End Function
Using the other rules for weekdays, we know we only need to deal with the exceptions, to find out whether there are 5 weekdays in a month:
Private Shared Function NumberOfWeekdaysInMonth(ByVal weekday As DayOfWeek, ByVal referenceDate As Date) As Integer Dim firstDay As DayOfWeek Dim lastDay As DayOfWeek firstDay = New Date(referenceDate.Year, referenceDate.Month, 1).DayOfWeek lastDay = New Date(referenceDate.Year, referenceDate.Month, 1).AddMonths(1).AddDays(-1).DayOfWeek If New Date(referenceDate.Year, referenceDate.Month, 1).AddMonths(1).AddDays(-1).Day = 28 Then Return 4 ElseIf lastDay >= firstDay AndAlso weekday >= firstDay AndAlso weekday <= lastDay Then Return 5 ElseIf lastDay < firstDay AndAlso weekday >= firstDay - 7 AndAlso weekday <= lastDay Then Return 5 ElseIf lastDay < firstDay AndAlso weekday >= firstDay AndAlso weekday <= lastDay + 7 Then Return 5 Else Return 4 End If End Function
That one got a bit hairy because is the extra days started at the end of the week with a high DayOfWeek value, and ended early in the week with a low DayOfWeek value, we had to compensate at each end. That’s the reasons for all the different IF conditions. There’s also a specific condition for February’s 28 days.
Finally, a function to determine the first, second, third, fourth, or fifth weekday in a month. Useful when calculating holidays like Labor Day and Thanksgiving.
Private Shared Function NthDayOfMonth(ByVal index As Integer, ByVal weekDay As DayOfWeek, _ ByVal referenceDate As Date) As Date Dim firstDay As DayOfWeek Dim dayOfMonth As Integer firstDay = New Date(referenceDate.Year, referenceDate.Month, 1).DayOfWeek dayOfMonth = (7 * (index - 1)) + _ CInt(IIf(firstDay > weekDay, 7 + weekDay - firstDay, weekDay - firstDay)) Return New Date(referenceDate.Year, referenceDate.Month, dayOfMonth + 1) End Function