Occurrences of a day of week (Excel 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote