Introduction
PROBLEM: I needed to calculate the date for the same weekday of the month 1 year prior.
I recently encountered a date calculation scenario that I had never seen before. I did my typical web searches and found nothing similiar, so I put my thinking cap on and went to work. I work for a hotel management company. We are working on an occupancy reporting system. The request was to calculate how a hotel did on "this weekday of the month" last year. For example: if today is the 3rd Sunday of January, how the hotel do on the 3rd Sunday of January last year? This proved to be a more challenging task than I had thought.
I could have used a loop to roll through each month, but I have several months to generate, so this didn't really seem like a good idea.
I also thought about calculating based on the week of the year. But the difference in weeks in the month left me scratching my head.
I realized that I had some fixed information, and could likely quickly calculate the date and therefore avoid looping 1-31 x 365 times each screen refresh.
Using the code
The fixed information that we know is:
- Inputed Month
- Inputed Year
- Target Month
- Target Year
- Days In the Inputed Month
- Days In the Target Month
- There are 7 days in a week.
The calculation simply outputs a number 1 through 31 for the day of the target month. Since all months don't begin on Sunday and look different each year, I calculated the "offset" of each month. This was the calculated by the using the DayOfWeek function for the 1st of each month.
To start with I loaded a few variables with the known information.
iInMonth = dInDate.Month
iInYear = dInDate.Year
iInWeekDay = dInDate.DayOfWeek
dInWeek = Math.Ceiling((dInDate.Day + iInOffset) / 7)
iInOffset = New Date(iInYear, iInMonth, 1).DayOfWeek
iOutOffset = New Date(iInYear - 1, iInMonth, 1).DayOfWeek
Then I was able to calculate the potential target date using the following formula.
X = ((Week Passed in * 7 days in a week ) - (6 - WeekDay Passed In) ) - Output Offset
breaking it down:
(Week Passed In x 7 Days in a week) will give me the Saturday of the target week
(6 - Week Day Passed In) will subtract from Saturday to give me the correct weekday
Subtracting the Offset of the output month will adjust for the differences in the months
So the calculation to get the correct weekday was:
dDayCalc = ((dInWeek * 7) - (6 - iInWeekDay)) - iOutOffset
This almost works, but I found some scenarios that need to be accounted for.
1st - The calculation could return a NON date outside of our 1-31 calculation. The partial weeks at the beginning of the month sometimes pushed my calculate back into the previous month. AKA negative DAY calculated. For example if there was no Friday of the first week of the month instead the first week started on Saturday, it will return a negative.
2nd - Leap year can change how many weeks are in a month. This causes problems particularly in February 2008 (5 weeks) and February 2009 (4 weeks).
3rd - The calculated date could have gone past the end of the month. Leap year is another example.
If dDayCalc <= 0 Then
dDayCalc += 7
ElseIf (Date.DaysInMonth(dInDate.Year - 1, dInDate.Month) / 7) > (Date.DaysInMonth(dInDate.Year, dInDate.Month) / 7) _
And iInWeekDay < iOutOffset Then
dDayCalc += 7
ElseIf dDayCalc > Date.DaysInMonth(dInDate.Year - 1, dInDate.Month) Then
Return Nothing
End If
Conclusion
It turned out to be a fairly easy task, but just needed some thinking. I'm sure there are many other ways to accomplish the taks, but this seemed to make sense to me at this time. The project files included are a quick and dirty VB.NET windows app to test the dates. I hope this helps someone else.
