Results 1 to 15 of 19

20090321, 21:07 #1
 Join Date
 Oct 2003
 Location
 Guelph, Ontario, Canada
 Posts
 428
 Thanks
 0
 Thanked 0 Times in 0 Posts
Using Excel 2003
A club meets every Thursday. Their fiscal year runs from October 1 through September 30.
Therefore, when you exclude Christmas and New Years days when they fall on Thursday, some fiscal years have 50 meeting days, some have 51 and some have 52.
This has to do with calculating perfect attendance.
Therefore my question...
Is there an Excel formula for calculating the number of Thursdays, excluding Christmas and New Years days when they fall on Thursday, in that fiscal year setup  Oct. 1 through Sept. 30?
I know how to get this, one year at a time, in Access. However, I was hoping to find an Excel formula and just fill down the cells in a column.
Thanks.
Tom

20090322, 03:38 #2
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='THWatson' post='766657' date='22Mar2009 10:07']Using Excel 2003
A club meets every Thursday. Their fiscal year runs from October 1 through September 30.
Therefore, when you exclude Christmas and New Years days when they fall on Thursday, some fiscal years have 50 meeting days, some have 51 and some have 52.
This has to do with calculating perfect attendance.
Therefore my question...
Is there an Excel formula for calculating the number of Thursdays, excluding Christmas and New Years days when they fall on Thursday, in that fiscal year setup  Oct. 1 through Sept. 30?
I know how to get this, one year at a time, in Access. However, I was hoping to find an Excel formula and just fill down the cells in a column.
Thanks.
Tom[/quote]
Hi
The attached consist of 2 methods
the 1st made use of the Weekday function and return the numbers assosciates with the weekday
1 = Sunday, 2= Monday and so on
then I use the Counif function with the Count to arrive the numbers of Thursdays for the period
=COUNTIF(B2:B366,"=5")COUNT(Holidays)
the 2nd method mad euse of the Excel builtin features
Type the date for 1st Thursday in E2
Choose Edit  Fill  Series. Excel displays the Series dialog box.
Choose Columns in Series
Choose Date in Type
Choose Day in Date Unit
Type 7 in Step value
OK out
The result is that Excel fills all the selected cells with dates that are 7 days apart from each
other.
I use the Count with Counta to count all Thursday
=COUNTA(E2:E53)COUNT(Holidays)
On both methods, Holidays is a named range consist of the 2 holidays, ie New Year and X'mas days
You may hide all the helper columns.Hope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090322, 04:44 #3
 Join Date
 Oct 2003
 Location
 Guelph, Ontario, Canada
 Posts
 428
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='franciz' post='766665' date='22Mar2009 08:38']Hi
The attached consist of 2 methods
the 1st made use of the Weekday function and return the numbers assosciates with the weekday
1 = Sunday, 2= Monday and so on
then I use the Counif function with the Count to arrive the numbers of Thursdays for the period
=COUNTIF(B2:B366,"=5")COUNT(Holidays)
the 2nd method mad euse of the Excel builtin features
Type the date for 1st Thursday in E2
Choose Edit  Fill  Series. Excel displays the Series dialog box.
Choose Columns in Series
Choose Date in Type
Choose Day in Date Unit
Type 7 in Step value
OK out
The result is that Excel fills all the selected cells with dates that are 7 days apart from each
other.
I use the Count with Counta to count all Thursday
=COUNTA(E2:E53)COUNT(Holidays)
On both methods, Holidays is a named range consist of the 2 holidays, ie New Year and X'mas days
You may hide all the helper columns.[/quote]
Thanks, franciz. Much appreciated!!
Tom

20090322, 09:56 #4
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='THWatson' post='766666' date='22Mar2009 17:44']Thanks, franciz. Much appreciated!!
Tom[/quote]
Hi Tom
Thank you for your feedback
Here another shorter and direct way to do it without all the intermediates
Assuming
B2 is Start Date : 10/1/2008
C2 is End Date : 9/30/2009
Enter this formula in D2
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(B2&":"&C2)))=5))IF(WEEKDAY("1/1/" &
YEAR(C2))=5,2,0)
HTHHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090322, 19:42 #5
 Join Date
 Oct 2003
 Location
 Guelph, Ontario, Canada
 Posts
 428
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='franciz' post='766685' date='22Mar2009 15:56']Hi Tom
Thank you for your feedback
Here another shorter and direct way to do it without all the intermediates
Assuming
B2 is Start Date : 10/1/2008
C2 is End Date : 9/30/2009
Enter this formula in D2
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(B2&":"&C2)))=5))IF(WEEKDAY("1/1/" &
YEAR(C2))=5,2,0)
HTH[/quote]
Yep, that helps. Thanks franciz.
Tom

20090322, 19:59 #6
 Join Date
 Oct 2003
 Location
 Guelph, Ontario, Canada
 Posts
 428
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='THWatson' post='766752' date='23Mar2009 00:42']Yep, that helps. Thanks franciz.
Tom[/quote]
FRANCIZ
For whatever reason, this gives me a #REF! error.
Tom

20090323, 11:17 #7
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='THWatson' post='766753' date='23Mar2009 08:59']FRANCIZ
For whatever reason, this gives me a #REF! error.
Tom[/quote]
The formula works fine
check that
you are referencing the correct cells and
2nd, your dates is a real dates and not text that look like dates
go to format cells
choose Date, check that it is US dates
see attached
HTHHope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090323, 14:18 #8
 Join Date
 Oct 2003
 Location
 Guelph, Ontario, Canada
 Posts
 428
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='franciz' post='766852' date='23Mar2009 16:17']The formula works fine
check that
you are referencing the correct cells and
2nd, your dates is a real dates and not text that look like dates
go to format cells
choose Date, check that it is US dates
see attached
HTH[/quote]
Dagnabbit, franciz...me dumb!
I was using B2 and C2 but had the actual cells in B1 and C1.
Sorry to bug you. Thanks for straightening me out.
Tom

20090323, 15:47 #9
 Join Date
 Oct 2003
 Location
 Guelph, Ontario, Canada
 Posts
 428
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='THWatson' post='766882' date='23Mar2009 19:18']Dagnabbit, franciz...me dumb!
I was using B2 and C2 but had the actual cells in B1 and C1.
Sorry to bug you. Thanks for straightening me out.
Tom[/quote]
An interesting little wrinkle, franciz.
If you take October 1, 1975 as A2 and September 30, 1976 as B2, you get 51 Thursdays. But there are actually 52 Thursdays. Hmmmm....
Tom

20090324, 05:33 #10
 Join Date
 Oct 2003
 Location
 Guelph, Ontario, Canada
 Posts
 428
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='THWatson' post='766895' date='23Mar2009 20:47']An interesting little wrinkle, franciz.
If you take October 1, 1975 as A2 and September 30, 1976 as B2, you get 51 Thursdays. But there are actually 52 Thursdays. Hmmmm....
Tom[/quote]
franciz
Also, if you take October 1, 1992 as A2 and September 30, 1993 as Be, you get 53 Thursdays. But there are actually 52 Thursdays.
Is there a way to tweak the formula so that it produces accurate results?
Tom

20090324, 07:08 #11
 Join Date
 Oct 2003
 Location
 Guelph, Ontario, Canada
 Posts
 428
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='THWatson' post='766993' date='24Mar2009 10:33']franciz
Also, if you take October 1, 1992 as A2 and September 30, 1993 as Be, you get 53 Thursdays. But there are actually 52 Thursdays.
Is there a way to tweak the formula so that it produces accurate results?
Tom[/quote]
franciz
The problem is that the formula isn't removing Thursdays when they fall on Christmas or New Years.
However, I can swing the 2 date columns (Start and End) into an Access table, then use a function in a query to get the correct # of Thursdays in the fiscal year. The fiscal year runs from Oct 1 of one year through September 30 of the next.
Thanks.
Tom

20090324, 07:44 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
From 10/01/75 to 09/30/76 there were 53 Thursdays, but since both Christmas Day 1975 and New Year's Day 1976 were Thursdays, only 51 remain.
From 10/01/92 to 09/30/93 there were 53 Thursdays, and since neither Christmas Day 1992 nor New Year's Day 1993 was a Thursday, the total is 53.

20090324, 10:51 #13
 Join Date
 Oct 2003
 Location
 Guelph, Ontario, Canada
 Posts
 428
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='767008' date='24Mar2009 12:44']From 10/01/75 to 09/30/76 there were 53 Thursdays, but since both Christmas Day 1975 and New Year's Day 1976 were Thursdays, only 51 remain.
From 10/01/92 to 09/30/93 there were 53 Thursdays, and since neither Christmas Day 1992 nor New Year's Day 1993 was a Thursday, the total is 53.[/quote]
Well, Hans, you're right. A manual count confirms that.
I was using the following formula in Access to test against...and it's obvious I have to modify the formula as it doesn't allow more than 52.
[codebox]WeekCount: IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 And Weekday(DateSerial(Year([MeetingDate]),1,1))=5,50,IIf(Weekday(DateSerial(Year([M
eetingDate]),12,25))=5 Or Weekday(DateSerial(Year([MeetingDate]),1,1))=5,51,52))[/codebox]
Tom

20090324, 11:33 #14
 Join Date
 Nov 2005
 Location
 Asia Pacific, Bangkok Metropolis
 Posts
 378
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='franciz' post='767049' date='25Mar2009 00:30']Hans
Thank for helping to clarify
Tom
I believe that the formula is working correctly to your requirements
cheers[/quote]Hope this is helpful
francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
My Reading
Pivot Table 101
Pivot Table
Array

20090324, 12:04 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
If A1 has the year for the first date, you can use this to calculate. Then you can just fill A2: Awhatever with other dates and copy down the column
=((DATE(A1+1,9,30)WEEKDAY(DATE(A1+1,9,30))(WEEKDAY(DATE(A1+1,9,30))<5)*7)(DATE(A1,10,1)WEEKDAY(DATE(A1,10,1))+(WEEKDAY(DATE(A1,10,1))>5)* 7))/7+12*(WEEKDAY(DATE(A1,12,25))=5)
Steve