# Thread: Formula to calculate Thursdays in a fiscal year, excluding Christmas and New Years

1. 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

2. [quote name='THWatson' post='766657' date='22-Mar-2009 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 built-in 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.

3. [quote name='franciz' post='766665' date='22-Mar-2009 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 built-in 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

4. [quote name='THWatson' post='766666' date='22-Mar-2009 17:44']Thanks, franciz. Much appreciated!!

Tom[/quote]

Hi Tom

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

5. [quote name='franciz' post='766685' date='22-Mar-2009 15:56']Hi Tom

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

6. [quote name='THWatson' post='766752' date='23-Mar-2009 00:42']Yep, that helps. Thanks franciz.

Tom[/quote]
FRANCIZ
For whatever reason, this gives me a #REF! error.

Tom

7. [quote name='THWatson' post='766753' date='23-Mar-2009 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

HTH

8. [quote name='franciz' post='766852' date='23-Mar-2009 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

9. [quote name='THWatson' post='766882' date='23-Mar-2009 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

10. [quote name='THWatson' post='766895' date='23-Mar-2009 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

11. [quote name='THWatson' post='766993' date='24-Mar-2009 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

12. 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.

13. [quote name='HansV' post='767008' date='24-Mar-2009 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

14. [quote name='franciz' post='767049' date='25-Mar-2009 00:30']Hans

Thank for helping to clarify

Tom

I believe that the formula is working correctly to your requirements

cheers[/quote]

15. 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+1-2*(WEEKDAY(DATE(A1,12,25))=5)

Steve

Page 1 of 2 12 Last

#### Posting Permissions

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