# Thread: number of Thursdays in a year excluding holidays (2000 all updates)

1. ## number of Thursdays in a year excluding holidays (2000 all updates)

Using Access 2000

A club meets every Thursday. If a member attends all 52 Thursdays in a given year, he/she receives Perfect Attendance credit for that year. So, calculating whether or not the member achieved perfect attendance for a given year is relatively easy - compare the # of meetings attended to the number of weeks in that year.

However, in certain years (e.g. 2008) both Christmas and New Years days fell on Thursday. In those years, a member receives Perfect Attendance credit if she/he attended 50 meetings that year.

Is there a formula or function that calculates the number of Thursdays in a year excluding holidays?

Thanks.

Tom

2. ## Re: number of Thursdays in a year excluding holidays (2000 all updates)

Create a table tblHolidays in your database with a date/time field Holiday.
Fill the table with holidays for the time period you want to cover, e.g. 2008-2010.

Here is a series of VBA functions you can use:
<code>
' Arguments:
' BegDate: beginning date
' EndDate: ending date
' aDay: day of the week; 1 = Sunday, 2 = Monday, ...
' In code, use vbSunday, vbMonday, ...
' Example:
' CountDaysBetween(#12/20/2008#,#1/10/2009#,5) counts the number of Thursdays
' in the period from December 20, 2008 through January 10, 2009. Returns 3.

Function CountDaysBetween(BegDate As Date, EndDate As Date, aDay As Integer) As Integer
Dim d As Integer
If BegDate > EndDate Then Exit Function
d = (EndDate - BegDate) 7
d = d + 1
End If
CountDaysBetween = d
End Function

' Arguments:
' BegDate: beginning date
' EndDate: ending date
' aDay: day of the week; 1 = Sunday, 2 = Monday, ...
' In code, use vbSunday, vbMonday, ...
' Example:
' CountHolidaysBetween(#12/20/2008#,#1/10/2009#,5) counts the number of holidays
' that fall on a Thursday in the period from December 20, 2008 through January 10, 2009. Returns 2.

Function CountHolidaysBetween(BegDate As Date, EndDate As Date, aDay As Integer) As Integer
If BegDate > EndDate Then Exit Function
CountHolidaysBetween = DCount("*", "tblHolidays", "WeekDay(Holiday)=" & aDay & _
" AND Holiday Between #" & Format(BegDate, "mm/dd/yyyy") & _
"# And #" & Format(EndDate, "mm/dd/yyyy") & "#")
End Function

' Arguments:
' BegDate: beginning date
' EndDate: ending date
' aDay: day of the week; 1 = Sunday, 2 = Monday, ...
' In code, use vbSunday, vbMonday, ...
' Example:
' CountDaysBetweenEx(#12/20/2008#,#1/10/2009#,5) counts the number of Thursdays
' in the period from December 20, 2008 through January 10, 2009, excluding holidays. Returns 1.

Function CountDaysBetweenEx(BegDate As Date, EndDate As Date, aDay As Integer) As Integer
If BegDate > EndDate Then Exit Function
CountDaysBetweenEx = CountDaysBetween(BegDate, EndDate, aDay) - _
End Function
</code>
To count the number of Thursdays in 2008 excluding holidays:

CountDaysBetweenEx(#1/1/2008#,#12/31/2009#,5)

3. ## Re: number of Thursdays in a year excluding holidays (2000 all updates)

Hans

I never, ever received this reply. So I came on the site today, and poked through all of the posts and finally found this.

When I look at the notice board I see that there wre a couple of problems around the time this post was made...
(1) server busy by times

I also had found it difficult to log on for a period of a week or so, and see that is now explained.

This was the solution I came up with...where you enter the time frame for which you wish to check.
<code>SELECT IIf(Weekday(DateSerial(Year([Enter Start Date]),12,25))=5 And Weekday(DateSerial(Year([Enter End Date]),1,1))=5,50,IIf(Weekday(DateSerial(Year([Enter Start Date]),12,25))=5 Or Weekday(DateSerial(Year([Enter End Date]),1,1))=5,51,52)) AS WeekCount;</code>

Tom

4. ## Re: number of Thursdays in a year excluding holidays (2000 all updates)

Yes, we were struggling with e-mail notifications a month ago; you should get them correctly now.

5. ## Re: number of Thursdays in a year excluding holidays (2000 all updates)

Hans

I knew something had to be wrong when I didn't hear back from you - but then when I couldn't get on the site for a week or so I couldn't figure out what was going on.

Tom

6. ## Re: number of Thursdays in a year excluding holidays (2000 all updates)

I don't understand why you couldn't log in for a week - although the server is slow at times, we've been up and running all the time...

7. ## Re: number of Thursdays in a year excluding holidays (2000 all updates)

Hans
I don't understand either. But that was a month ago. Doesn't seem to be a problem now.

Tom

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•