Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Occurrences of a day of week (Excel 2003)

    I need some help writing a formula that will count the number of Tues, etc. within a specified date range. The date range is listed in E2 and F2.

    Thanks for the help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Occurrences of a day of week (Excel 2003)

    You can use the following custom VBA function:

    Function DOWBetween( _
    ByVal StartDate As Date, _
    ByVal EndDate As Date, _
    ByVal DOW As Integer) As Integer
    Dim d As Date
    DOWBetween = (EndDate - StartDate) 7
    EndDate = EndDate - 7 * DOWBetween
    For d = StartDate To EndDate
    If Weekday(d) = DOW Then
    DOWBetween = DOWBetween + 1
    End If
    Next d
    End Function

    If you store it in a module in the workbook itself, you can use it like this in C2:

    =DOWBetween(MinDate,MaxDate,B2)

    and fill down. If you store the function in a module in your personal macro workbook Personal.xls, change the formula to

    =Personal.xls!DOWBetween(MinDate,MaxDate,B2)

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Occurrences of a day of week (Excel 2003)

    Or, you can try a formula way.

    Cell C2, entered the following formula and copied down to Cell C6 :

    =INT((MaxDate-B2)/7)-INT((MinDate-B2)/7)

    Regards
    Bosco

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Occurrences of a day of week (Excel 2003)

    Your formula is clever and interesting, but it doesn't include the start date in the count. For example, MinDate in the sample workbook is a Friday. If you count the number of Fridays (DOW = 6), you'll see that the count is too low by 1.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Occurrences of a day of week (Excel 2003)

    Marie

    A another way to do this without VBA is to use this formula:

    = SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(MinDate&":"&MaxDate)))=2))

    Where Sunday = 1 and Saturday = 7

    I have attached your original workbook with my adaptations.
    Jerry

Posting Permissions

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