# Thread: Occurrences of a day of week (Excel 2003)

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

2. ## 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. ## 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. ## 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. ## 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.

#### Posting Permissions

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