Counting dates in current month

[Tmoske]

How can I calculate how many cells in a column have the date that falls in the current month.

Thanks,

 

[barry houdini]

Hell Tmoske, welcome to MrExcel

Here's one way

=SUMPRODUCT(--(TEXT(A1:A100,"mmmyy")=TEXT(TODAY(),"mmmyy")))

or, if no dates are future dates you can try

=COUNTIF(A1:A100,">"&TODAY()-DAY(TODAY()))

I'm assuming your dates are in the range A1:A100, change as necessary

 

[Domenic]

Here's another way...

Assuming that A2:A100 contains the data, and B2 contains today's date, try...

=SUMPRODUCT(--(A2:A100-DAY(A2:A100)+1=DATE(YEAR(B2),MONTH(B2),1)))

Hope this helps!

 

[barry houdini]

Hello Domenic,

If you go that way wouldn't this be simpler?

=SUMPRODUCT(--(A2:A100-DAY(A2:A100)=B2-DAY(B2)))

 

[Tmoske]

Thanks guys,
All methods worked fine but, I did need to reference a certain cell for the current month.

Thank you all for the help!

 

[Leith Ross]

Hello Tmoske,

If your are looking for VBA macro solution, which can also be used as worksheet formula, try this. The first argument is the range that has dates. Cells that aren't dates are ignored. The second argument is optional to specify the month you want. This is an integer from 1 to 12. If you don't specify a month then the current month is used.

Function CountDatesByMonth(ByRef Rng As Range, Optional ByVal M As Integer) As Long

  Dim Cell As Range
  Dim Cnt As Long
  
    If M = 0 Then
       M = Month(Now())
    End If
    
    For Each Cell In Rng
      If IsDate(Cell) Then
        If Month(Cell) = M Then Cnt = Cnt + 1
      End If
    Next Cell
    
    CountDatesByMonth = Cnt
    
End Function

Sincerely,
Leith Ross

 

[Domenic]

Hello Domenic,

If you go that way wouldn't this be simpler?

=SUMPRODUCT(--(A2:A100-DAY(A2:A100)=B2-DAY(B2)))

Hi Barry,

Most definitely...

 

[Tmoske]

You guys sure exceeded my expectations. Here is another one I need.

Column A has dates, Column B has dates. I need to count how many dates in colomn B are greater than 10 days of Column A for a certain month.

Thanks,

 

[Domenic]

Try...

=SUMPRODUCT(--(A2:A100-DAY(A2:A100)+1=B2-DAY(B2)+1),--(B2:B100>A2:A100+10))

...where B2 contains any date within the month of interest.

Hope this helps!