Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / VB

Weekday of Month Calculation

2.89/5 (5 votes)
26 Dec 2007CPOL3 min read 1   283  
Calculate this same weekday of month last year

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.

VB.NET
iInMonth = dInDate.Month

iInYear = dInDate.Year

iInWeekDay = dInDate.DayOfWeek

dInWeek = Math.Ceiling((dInDate.Day + iInOffset) / 7) '*** CALCULATE THE WEEK OF THE MONTH ****

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:

VB.NET
'*********************************

'*** CALCULATE THE TARGET DATE ***

'*********************************

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.

VB.NET
'*************************

'**** ANOMALY CATCHES ****

'*************************

If dDayCalc <= 0 Then

'*** CALCULATION RETURNS NON DATE

dDayCalc += 7

ElseIf (Date.DaysInMonth(dInDate.Year - 1, dInDate.Month) / 7) > (Date.DaysInMonth(dInDate.Year, dInDate.Month) / 7) _

And iInWeekDay < iOutOffset Then

'*** IF THERE ARE MORE WEEKS IN THE OUTMONTH THAN THE 

'*** IN MONTH WE NEED TO ADD 1 WEEK. 

'*** THIS OCCURS AS A RESULT OF LEAP YEAR FOR EXAMPLE FEB 2009 - FEB 2008 

dDayCalc += 7

ElseIf dDayCalc > Date.DaysInMonth(dInDate.Year - 1, dInDate.Month) Then

'*** CALCULATION RETURNS DATE MORE THAN IN MONTH

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)