Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    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

  2. #2
    3 Star Lounger
    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='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.
    Attached Files Attached Files
    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

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #4
    3 Star Lounger
    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='22-Mar-2009 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)

    HTH
    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

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='franciz' post='766685' date='22-Mar-2009 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

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

  8. #8
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #10
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #11
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

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

  15. #15
    WS Lounge VIP sdckapr's Avatar
    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+1-2*(WEEKDAY(DATE(A1,12,25))=5)

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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