Nth Day

This week I want to share with you a function that I personally got a lot of use out of. Several years ago I had a situation where there was a calendar with several hundreds recurring events. It was desired to have these events fall on roughly the same day each year. The person responsible for this was spending two or three days at the end of each year planning all the occurrences for the next year on a giant wet-eraseable calendar.

Here's what all the fuss was about: Last year, November 1 was a Thursday. This year, November 1 is a Saturday. Saturday is not a working day for most people, so these events we are scheduling need to fall on week days. Because it fell on a Thursday last year, we'd prefer to have it on a Thursday again this year. This means either pushing it back to October 30 or forward to November 6.

To make the event occur on the same day of the week, we'll store this information in our database and use it to generate the date for a given year. This is called the Nth Day. In the above example, our Nth Day is the first Thursday in November.

ASP

1. function nthDay(someYear, someMonth, someWeek, someWeekday)
2.     dim firstDay
3.     dim someDay
5.     firstDay = weekday(dateSerial(someYear, someMonth, 1))
7.     ' Check if the week day of the first day of the month is before or after the given week day.
8.     if (someWeekday - firstDay) >= 0 then
9.         someDay = 1 + (someWeekday - firstDay) + ((someWeek - 1) * 7)
10.     else
11.         someDay = 1 + (someWeekday - firstDay) + (someWeek * 7)
12.     end if
14.     nthDay = dateSerial(someYear, someMonth, someDay)
15. end function

If developing a system around this concept like I did, it's important to know that some months have a fifth week, depending on how late in the week the first day of the month is. You can't really schedule anything recurring during the fifth week. We kept it open both to give the people conducting the events a little time off, as well as for a place to move an event from the following month in situations where the customer thought it would be too late in the year otherwise.

An idea I had to improve the system, but didn't get around to exploring, was to ignore the months entirely and use the weeks of the year. Nobody wants to schedule anything around Christmas and New Year's anyway, so you don't run into problems around the beginning/end of the year like you do at the beginning/end of a month. Under this system, first Thursday of November becomes Thursday of Week 44. Scheduling a recurring event this way is not only easier, but also more consistent from year to year.

View ASP implementation on Snipplr