MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel date formula


Posted by adsl5122 on January 09, 2002 9:44 PM

I have a single column with various dates on it in
3/4/2000 format. I'm looking for a formula that counts the number of occurences for a given month in a particular year.Thanks.


Posted by Jacob on January 09, 2002 10:02 PM

Hi

This will do the trick:

Sub CountMonths()

Dim x As Integer
Dim Counter As Integer

For x = 1 To Range("A65536").End(xlUp).Row

Select Case Application.WorksheetFunction.Text(Range("A" & x).Value, "MMMM")

Case "March"

Counter = Counter + 1

Case Else

End Select

Next x

MsgBox ("There are " & Counter & " dates in March")

End Sub

Also you could make another row (and hide it) and have =text("A1","MMMM") then fill down

Then do a countif on col B for "March"

HTH

Jacob