Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Thursdays in a year excluding Christmas and New Years 1

Status
Not open for further replies.
THWatson

THWatson

Technical User
Apr 25, 2000
2,601
CA
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
 
Sort by date Sort by votes
How about keeping a small table of holidays and deducting one from 52 for each holiday that occurs on Thursday? I have some code for calculating Easter, if that holiday is relevant.

 
Upvote 0 Downvote
Remou
Thanks for the suggestion.

Let me take a run at that approach. I guess that the table would only need to have 12/25 and 1/1 in it.

Since Easter would never fall on a Thursday, the Easter holiday wouldn't be relevant. Unless I'm missing something in my thinking.
However, could you post the Easter calculation code, because I might be able to use it elsewhere.

Thanks.

Tom
 
Upvote 0 Downvote
Upvote 0 Downvote
Remou
Where I am - Canada - although we celebrate St. Patrick's Day it is not a holiday (at least not a statutory holiday) and I don't know of anyone who has a holiday from work on that day. There are 8 Federal statutory holidays, and then some provinces have additional statutory holidays (e.g. where I am - Ontario - we have one additional, making a total of 9).

When I think of Easter, I think of Easter Sunday. Good Friday is a statutory holiday, but since that's not a Thursday...

We don't refer to St. Stephen's Day here - which, if I'm not mistaken, falls on December 26. But we do have Boxing Day which is December 26, and that is a statutory holiday.
All of which makes me ask whether or not the club meets on Thursday if Boxing Day falls on a Thursday. If so, that would mean there would be 51 meeting days in that year.
I will have to inquire about that.

In any event, thanks for the formula, and for the link.

Much appreciated.

Tom
 
Upvote 0 Downvote
However, in certain years (e.g. 2008) both Christmas and New Years days fell on Thursday
Click to expand...
On my calendar:

Jan 1, 2008 -- Tuesday
Dec 25, 2008 -- Thursday

Jan 1, 2009 -- Thursday
Dec 25, 2009 -- Friday


Randy
 
Upvote 0 Downvote
randy700

[blush] I was think of the New Year following Christmas, that ism next year.

Code: 
WeekCount = IIf(Weekday(DateSerial(Year(Date), 12, 25)) = 5 _
And Weekday(DateSerial(Year(Date), 1, 1)) = 5, 50, _
IIf(Weekday(DateSerial(Year(Date), 12, 25)) = 5 _
Or Weekday(DateSerial(Year(Date), 1, 1)) = 5, 51, 52))

 
Upvote 0 Downvote
Randy
Right. However, in this particular case, the fiscal year for the club runs from October through September. (and I should have indicated that before)

That would seem to indicate that the formula is correct as is. Or am I missing something?

Tom
 
Upvote 0 Downvote
If the weeks in question are Christmas of a year and New Year of the next year, then the first example is correct as there are seven days between the two.

 
Upvote 0 Downvote
Well, Alice, this thing gets curiouser and curiouser!"

Upon finding further information, turns out that a club member is granted Perfect Attendance for attending the appropriate number of meetings (could be 52, could be 50, depending on the cycle of his/her year being used) in any consecutive 12 month period.

So, I can't calculate from any specific data in time because Member A may be working on a different cycle from Member B, and each of those different from Member C, etc. on through all the members. Sounds like enough to give a fella nightmares.

Providing one had a Start Date for the cycle, a running total could be kept...but how the heck do you program in a start date when it's different for every member?

Any thoughts would be appreciated.

Thanks.

Tom
 
Upvote 0 Downvote
I modified the code a bit, in order to allow the user to select the dates for a period from a form.

The code is
Code: 
WeekCount: IIf(Weekday(DateSerial(Year([Forms]![frmDateSelector]![txtStartDate]),12,25))=5 And Weekday(DateSerial(Year([Forms]![frmDateSelector]![txtEndDate]),1,1))=5,50,IIf(Weekday(DateSerial(Year([Forms]![frmDateSelector]![txtStartDate]),12,25))=5 Or Weekday(DateSerial(Year([Forms]![frmDateSelector]![txtEndDate]),1,1))=5,51,52))

That shows the number of Thursdays in a YEAR's time, excluding Christmas and New Years.

I would like also to be able to show the # of Thursdays within the selected time period, which could be less than a full year. The # of Thursdays would still exclude Christmas and New Years.

I have been working on the code for this, but haven't quite got it.

Tom
 
Upvote 0 Downvote
A custom function might be the best bet.

Code: 
Function RetThur(dteStart, dteEnd)
Dim intT As Integer
Dim dteTest As Date

    If dteStart >= dteEnd Then
        'Problem
        RetThur = "Error"
    End If
    
    For i = 0 To (dteEnd - dteStart)
        dteTest = dteStart + i
        ny = DateSerial(Year(dteTest), 1, 1)
        xm = DateSerial(Year(dteTest), 12, 25)
        
        If Weekday(dteTest) = 5 And dteTest <> ny And dteTest <> xm Then
            intT = intT + 1
        End If
    Next
    
    RetThur = intT
    
End Function


 
Upvote 0 Downvote
Remou
I had to declare additional variables...
Dim i As Integer
Dim ny As Date
Dim xm As Date

But when I run the function, it doesn't seem to matter what dates I input I still get a value of 0.

e.g.
? RetThur(12/28/08,1/10/09) returns 0, whereas I think it should return 1
? RetThur(1/11/08,1/25/09) returns 0, whereas I think it should return 2

Unless I am not doing something correctly.

Tom
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

caykamanj1966
  • Locked
  • Article
currentWeek excluding weekends
Replies
6
Views
161
strongm
strongm
spartansFC
  • Locked
  • Question
Display Year between 2 dates 1
Replies
1
Views
306
dhookom
dhookom
breezett93
  • Locked
  • Question
Brand new lock violations errors all over my database
Replies
11
Views
798
dhookom
dhookom
datajockey123
  • Locked
  • Question
Note / Information box with date and time stamp for each entery
Replies
2
Views
318
dhookom
dhookom
serino
  • Locked
  • Question
Find a number between two sets of numbers. 1
Replies
2
Views
524
serino
serino

Part and Inventory Search

Sponsor

Back
Top