Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
    If aDay < 1 Or aDay > 7 Then Exit Function
    d = (EndDate - BegDate) 7
    If WeekDay(EndDate, aDay) < WeekDay(BegDate, aDay) Or WeekDay(BegDate, aDay) = 1 Then
    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
    If aDay < 1 Or aDay > 7 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
    If aDay < 1 Or aDay > 7 Then Exit Function
    CountDaysBetweenEx = CountDaysBetween(BegDate, EndDate, aDay) - _
    CountHolidaysBetween(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. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hans
    Thanks for your suggestion.

    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
    (2) e-mail notification not working

    I guess that accounts for why I never received notification of your reply.

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

    Thanks again for your help.

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hans
    Yes, I received this reply from you.

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •